Query editor
-- The six largest live markets (the largest market of each collateral and limit) at the latest weekly reading: the share of supply held by the largest single supplier, in percent.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),u AS (SELECT chain, market_id, MAX(largest_share) AS largest_share, MAX(suppliers_with_5_percent) AS suppliers_with_5_percentFROM dune.paigeisonfire.result_borrow_collateral_suppliers_weekly WHERE week = (SELECT MAX(week) FROM dune.paigeisonfire.result_borrow_collateral_suppliers_weekly) GROUP BY 1, 2)SELECT l.short AS market, u.largest_share * 100 AS largest_share_pct,u.suppliers_with_5_percent, l.supplied_usdFROM live l JOIN u ON u.chain = l.chain AND u.market_id = l.market_idWHERE l.short_rank = 1ORDER BY l.supplied_usd DESC LIMIT 6