Query editor
WITH-- 1) Identify first swap vs. returning swapsswaps AS (SELECTblockchain,tx_hash,block_time as timestamp,taker as user,amount_usd,-- For each (blockchain, user), find which swap is the user's firstROW_NUMBER() OVER (PARTITION BY blockchain, takerORDER BY block_time, tx_hash) AS user_rn_ascFROM (SELECT * FROM sushiswap_agg.tradesUNION ALLSELECT * FROM dune.sushi_labs.result_rp_trades_spellbook_pr)-- Add WHERE clause here if you need to filter by blockchain or date range),-- 2) Aggregate monthlymonthly_agg AS (SELECTDATE_TRUNC('month', timestamp) AS month,-- SwapsCOUNT(*) AS swaps_total,COUNT_IF(user_rn_asc = 1) AS swaps_newbies,COUNT(*) - COUNT_IF(user_rn_asc = 1) AS swaps_returned,-- UsersCOUNT(DISTINCT user) AS users_total,COUNT(DISTINCT IF(user_rn_asc = 1, user, NULL)) AS users_new,COUNT(DISTINCT user)- COUNT(DISTINCT IF(user_rn_asc = 1, user, NULL)) AS users_returned,-- VolumeSUM(amount_usd) AS volume_total,SUM(IF(user_rn_asc = 1, amount_usd, 0)) AS volume_newbies,SUM(amount_usd)- SUM(IF(user_rn_asc = 1, amount_usd, 0)) AS volume_returnedFROM swapsGROUP BY 1),-- 3) Add cumulative metrics over monthsfinal AS (SELECTmonth,swaps_total,swaps_newbies,swaps_returned,users_total,users_new,users_returned,volume_total,volume_newbies,