Query editor
WITH weekly_data AS (SELECTDATE_TRUNC('week', block_time) as "Date",blockchain as "Chain",SUM(CAST(amount_usd AS DOUBLE)) AS "USD Volume",COUNT(DISTINCT tx_from) as DAU,count(distinct tx_hash) as tx,SUM(CAST(amount_usd AS DOUBLE)) / COUNT(DISTINCT tx_from) as "Avg Volume per User",SUM(CAST(amount_usd AS DOUBLE)) / COUNT(DISTINCT tx_hash) as "Avg Volume per Trade"FROMdex.tradesWHEREblock_time > DATE_TRUNC('day', NOW()) - INTERVAL '365' dayAND CASE WHEN '{{Filter_out_low_value_tx}}' = 'Y' THEN amount_usd > 0.1 ELSE amount_usd < 0.1 ENDGROUP BY 1, 2UNION ALLSELECTDATE_TRUNC('week', block_time) as "Date",'Solana' as "Chain",SUM(CAST(amount_usd AS DOUBLE)) AS "USD Volume",COUNT(DISTINCT trader_id) as DAU,count(*) as tx,SUM(CAST(amount_usd AS DOUBLE)) / COUNT(DISTINCT trader_id) as "Avg Volume per User",SUM(CAST(amount_usd AS DOUBLE)) / count(*) as "Avg Volume per Trade"FROMdex_solana.tradesWHEREblock_time > DATE_TRUNC('day', NOW()) - INTERVAL '365' dayand trader_id not in ('2HDozvLZ8JPC8tuj5gKicAX3UX92AaKqXKkNGTyQgxC5')AND CASE WHEN '{{Filter_out_low_value_tx}}' = 'Y' THEN amount_usd > 0.1 ELSE amount_usd < 0.1 ENDGROUP BY 1, 2)SELECT"Date","Chain","USD Volume",DAU,"Avg Volume per User",AVG("Avg Volume per User") OVER (PARTITION BY "Chain"ORDER BY "Date"ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS "4-Week Moving Avg Volume per User","Avg Volume per Trade",AVG("Avg Volume per Trade") OVER (PARTITION BY "Chain"ORDER BY "Date"ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS "4-Week Moving Avg Volume per Trade"FROMweekly_dataWHERE"Date" >= DATE '2024-01-01' AND "Date" <= DATE '2025-01-31'ORDER BY"Date", "Chain";