Query editor
WITH borrow_30d AS (SELECT CASE WHEN project LIKE 'aave%' THEN 'Aave' WHEN project LIKE 'morpho%' THEN 'Morpho' WHEN project LIKE 'euler%' THEN 'Euler'WHEN project LIKE 'spark%' OR project LIKE 'maker%' OR project LIKE 'sky%' THEN 'Sky / MakerDAO' END AS protocol,SUM(CASE WHEN transaction_type = 'borrow' THEN amount_usd ELSE 0 END) AS borrow_volume_30d,SUM(CASE WHEN transaction_type = 'repay' THEN amount_usd ELSE 0 END) AS repay_volume_30d,COUNT(DISTINCT borrower) AS unique_borrowers_30d, COUNT(*) AS borrow_events_30dFROM lending.borrowWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '60' DAY)AND block_time >= CURRENT_DATE - INTERVAL '30' DAYAND (project LIKE 'aave%' OR project LIKE 'morpho%' OR project LIKE 'euler%' OR project LIKE 'spark%' OR project LIKE 'maker%' OR project LIKE 'sky%')AND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10GROUP BY 1),supply_30d AS (SELECT CASE WHEN project LIKE 'aave%' THEN 'Aave' WHEN project LIKE 'morpho%' THEN 'Morpho' WHEN project LIKE 'euler%' THEN 'Euler'WHEN project LIKE 'spark%' OR project LIKE 'maker%' OR project LIKE 'sky%' THEN 'Sky / MakerDAO' END AS protocol,SUM(CASE WHEN transaction_type = 'deposit' THEN amount_usd ELSE 0 END) AS supply_volume_30d,COUNT(DISTINCT depositor) AS unique_depositors_30dFROM lending.supplyWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '60' DAY)AND block_time >= CURRENT_DATE - INTERVAL '30' DAYAND (project LIKE 'aave%' OR project LIKE 'morpho%' OR project LIKE 'euler%' OR project LIKE 'spark%' OR project LIKE 'maker%' OR project LIKE 'sky%')AND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10GROUP BY 1),combined AS (SELECT COALESCE(b.protocol, s.protocol) AS protocol, COALESCE(borrow_volume_30d,0) AS borrow_volume_30d,COALESCE(repay_volume_30d,0) AS repay_volume_30d, COALESCE(supply_volume_30d,0) AS supply_volume_30d,COALESCE(unique_borrowers_30d,0) + COALESCE(unique_depositors_30d,0) AS active_users_30d,COALESCE(borrow_events_30d,0) AS borrow_events_30d, COALESCE(borrow_volume_30d,0) * 0.003 AS estimated_fees_30dFROM borrow_30d b FULL OUTER JOIN supply_30d s ON b.protocol = s.protocol),sector_total AS (SELECT 'Lending (Total)' AS protocol, SUM(borrow_volume_30d), SUM(repay_volume_30d), SUM(supply_volume_30d),SUM(active_users_30d), SUM(borrow_events_30d), SUM(estimated_fees_30d) FROM combined)SELECT * FROM combined UNION ALL SELECT * FROM sector_total ORDER BY estimated_fees_30d DESC