Query editor
WITH borrow_daily AS (SELECT CAST(block_time AS date) AS block_date,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_usd,SUM(CASE WHEN transaction_type = 'repay' THEN amount_usd ELSE 0 END) AS repay_volume_usd,COUNT(DISTINCT borrower) AS unique_borrowers, COUNT(*) AS borrow_eventsFROM lending.borrowWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '90' DAY)AND block_time >= CURRENT_DATE - INTERVAL '90' 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, 2),supply_daily AS (SELECT CAST(block_time AS date) AS block_date,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_usd,SUM(CASE WHEN transaction_type = 'withdraw' THEN amount_usd ELSE 0 END) AS withdraw_volume_usd,COUNT(DISTINCT depositor) AS unique_depositorsFROM lending.supplyWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '90' DAY)AND block_time >= CURRENT_DATE - INTERVAL '90' 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, 2)SELECT COALESCE(b.block_date, s.block_date) AS block_date, COALESCE(b.protocol, s.protocol) AS protocol,COALESCE(b.borrow_volume_usd, 0) AS borrow_volume_usd, COALESCE(b.repay_volume_usd, 0) AS repay_volume_usd,COALESCE(s.supply_volume_usd, 0) AS supply_volume_usd, COALESCE(s.withdraw_volume_usd, 0) AS withdraw_volume_usd,COALESCE(b.unique_borrowers, 0) + COALESCE(s.unique_depositors, 0) AS active_users,COALESCE(b.borrow_events, 0) AS borrow_eventsFROM borrow_daily b FULL OUTER JOIN supply_daily s ON b.block_date = s.block_date AND b.protocol = s.protocolWHERE COALESCE(b.protocol, s.protocol) IS NOT NULLORDER BY block_date DESC, protocol