Query editor
-- One row per live market on the last full UTC day. Rates are yearly, in percent, before rewards. Loan-to-value and supplier figures are from the latest weekly reading.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),b AS (SELECT * FROM dune.paigeisonfire.result_borrow_collateral_borrower_ltv_weekly WHERE week = (SELECT MAX(week) FROM dune.paigeisonfire.result_borrow_collateral_borrower_ltv_weekly)),u AS (SELECT chain, market_id, MAX(largest_share) AS largest_share, MAX(suppliers_with_5_percent) AS n5 FROM 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.market, l.chain_name AS chain, l.collateral_class, COALESCE(l.driving_asset, '') AS price_driver,l.borrowed_usd, l.supplied_usd, l.utilization * 100 AS utilization_pct,l.borrow_rate_30d * 100 AS borrow_rate_30d_pct, l.rate_at_target * 100 AS rate_at_target_pct, l.lender_rate * 100 AS lender_rate_pct,p.spread_rate_at_target_30d * 1e4 AS spread_bps,l.haircut * 100 AS haircut_pct, (1 - l.lltv * LEAST(1.15, 1 / (1 - 0.3 * (1 - l.lltv)))) * 100 AS cushion_pct,c.volatility_365d * 100 AS volatility_365d_pct, c.cushion_in_daily_moves,b.debt_over_collateral * 100 AS debt_over_collateral_pct, b.debt_share_within_5_points * 100 AS debt_near_limit_pct,u.largest_share * 100 AS largest_supplier_pct, u.n5 AS suppliers_with_5_pct,CASE WHEN l.listed THEN 'yes' ELSE 'no' END AS listed, '0x' || lower(to_hex(l.market_id)) AS market_idFROM live lLEFT JOIN dune.paigeisonfire.result_borrow_collateral_market_spread_daily p ON p.chain = l.chain AND p.market_id = l.market_id AND p.day = l.dayLEFT JOIN dune.paigeisonfire.result_borrow_collateral_market_cushion_daily c ON c.chain = l.chain AND c.market_id = l.market_id AND c.day = l.dayLEFT JOIN b ON b.chain = l.chain AND b.market_id = l.market_idLEFT JOIN u ON u.chain = l.chain AND u.market_id = l.market_idORDER BY l.borrowed_usd DESC