Query editor
WITH mamo_deposits AS (SELECT * FROM dune.serotonin_data.mamo_deposits),time_series AS (SELECTDATE(block_time) AS date,symbol AS token,COUNT(DISTINCT user_address) AS unique_depositors,COUNT(*) AS deposit_count,SUM(amount_usd) FILTER(WHERE action = 'deposit') AS usd_deposited,SUM(amount_usd) FILTER(WHERE action = 'withdraw') AS usd_withdrew,SUM(amount) AS net_amount_deposited,SUM(amount_usd) AS net_usd_depositedFROM mamo_depositsGROUP BY 1, 2),dates AS (SELECTdays,token_typeFROM (SELECT token_type FROM UNNEST(ARRAY['USDC', 'cbBTC', 'MAMO', 'WETH']) AS t(token_type)) tokensCROSS JOINUNNEST(SEQUENCE((SELECT DATE(MIN(date)) FROM time_series),(SELECT DATE(MAX(date)) FROM time_series),INTERVAL '1' DAY)) AS t(days)),agg_data AS (SELECT*,SUM(usd_deposited) OVER (PARTITION BY token ORDER BY date ASC) AS cumulative_deposited,SUM(usd_withdrew) OVER (PARTITION BY token ORDER BY date ASC) AS cumulative_withdrew,SUM(net_amount_deposited) OVER (PARTITION BY token ORDER BY date ASC) AS total_tokens--SUM(unique_depositors) OVER (PARTITION BY token ORDER BY date ASC) AS cumulative_unique_depositorsFROM (SELECTDATE_TRUNC('day', dt.days) AS date,dt.token_type as token,MAX(COALESCE(ts.unique_depositors, 0)) AS unique_depositors,SUM(COALESCE(ts.deposit_count, 0)) AS deposit_count,SUM(COALESCE(ts.usd_deposited, 0)) AS usd_deposited,SUM(COALESCE(ts.usd_withdrew, 0)) AS usd_withdrew,SUM(COALESCE(ts.net_amount_deposited, 0)) AS net_amount_deposited,SUM(COALESCE(ts.net_usd_deposited, 0)) AS net_usd_depositedFROM dates dtLEFT JOIN time_series ts ON dt.days = ts.date AND dt.token_type = ts.tokenGROUP BY 1, 2))SELECT*,total_tokens * prices.price AS TVLFROMagg_data aggLEFT JOIN