Query editor
-- Each supplier holding 5 percent or more of a live market at the latest weekly reading, with its onchain name where the contract reports one.WITH reg AS (SELECT * FROM dune.paigeisonfire.result_borrow_collateral_market_registry),last_day AS (SELECT MAX(day) AS day FROM dune.paigeisonfire.result_borrow_collateral_market_state_daily),live0 AS (SELECT r.chain, r.chain_name, r.market_id, r.market, r.collateral_symbol, r.collateral_class, r.driving_asset, r.lltv, r.haircut, r.listed,s.day, s.supplied_usd, s.borrowed_usd, s.utilization, s.borrow_rate, s.borrow_rate_7d, s.borrow_rate_30d, s.borrow_rate_90d,s.rate_at_target, s.rate_at_target_30d, s.lender_rateFROM dune.paigeisonfire.result_borrow_collateral_market_state_daily s JOIN last_day d ON d.day = s.dayJOIN reg r ON r.chain = s.chain AND r.market_id = s.market_idWHERE s.days_pinned = 0 AND s.borrowed_usd >= (CASE '{{Minimum debt}}' WHEN '$5M' THEN 5e6 WHEN '$25M' THEN 25e6 WHEN '$100M' THEN 100e6 ELSE 1e6 END) AND ('{{Chain}}' = 'All' OR r.chain_name = '{{Chain}}')AND NOT EXISTS (SELECT 1 FROM dune.paigeisonfire.result_borrow_collateral_market_exclusions x WHERE x.chain = s.chain AND x.market_id = s.market_id AND x.excluded_from <= s.day)),live AS (SELECT l.*, ROW_NUMBER() OVER (PARTITION BY l.short ORDER BY l.borrowed_usd DESC) AS short_rankFROM (SELECT l.*, CASE WHEN '{{Chain}}' = 'All' THEN substr(regexp_replace(l.collateral_symbol, '-[0-9]{1,2}[A-Z]{3}20[0-9]{2}$', ''), 1, 9) || CASE WHEN l.chain_name = 'Base' THEN ', Base' ELSE ', Eth' END ELSE substr(regexp_replace(l.collateral_symbol, '-[0-9]{1,2}[A-Z]{3}20[0-9]{2}$', ''), 1, 9) || ' ' || regexp_replace(format('%s', CAST(ROUND(l.lltv * 100, 1) AS DECIMAL(5,1))), '\.0$', '') || '%' END AS short FROM live0 l) l),nm AS (SELECT chain, supplier, name FROM dune.paigeisonfire.result_borrow_collateral_supplier_names_base UNION ALL SELECT chain, supplier, name FROM dune.paigeisonfire.result_borrow_collateral_supplier_names_ethereum)SELECT l.market, l.chain_name AS chain, COALESCE(n.name, 'no onchain name') AS supplier_name, u.supply_share * 100 AS share_pct, u.supplied_usd,CAST(u.supplier AS VARCHAR) AS supplier_addressFROM dune.paigeisonfire.result_borrow_collateral_suppliers_weekly uJOIN live l ON l.chain = u.chain AND l.market_id = u.market_idLEFT JOIN nm n ON n.chain = u.chain AND n.supplier = u.supplierWHERE u.week = (SELECT MAX(week) FROM dune.paigeisonfire.result_borrow_collateral_suppliers_weekly) AND u.supplier IS NOT NULL AND u.supply_share >= 0.05ORDER BY l.supplied_usd DESC, u.supply_share DESC