Query editor
-- Wallet-level feature summary: one row per aggregator-audience wallet.-- Spine: dune.serotonin_data.result_aggregator_audience (smart-contract and CEX wallets already excluded).-- Transfer metrics bounded to last 12 months (partition-pruned). Wallet age uses all-time first tx.WITH aud AS (SELECT wallet_address AS walletFROM dune.serotonin_data.result_aggregator_audience),dex AS (SELECT taker AS wallet,SUM(amount_usd) AS dex_vol_eth_12m,SUM(CASE WHEN block_date >= DATE_ADD('day', -30, CURRENT_DATE) THEN amount_usd END) AS dex_vol_eth_30dFROM dex.tradesWHERE blockchain = 'ethereum'AND block_date >= DATE_ADD('month', -12, CURRENT_DATE)AND taker IN (SELECT wallet FROM aud)GROUP BY taker),agg AS (SELECT taker AS wallet,SUM(amount_usd) AS agg_vol_eth_12m,SUM(CASE WHEN block_date >= DATE_ADD('day', -30, CURRENT_DATE) THEN amount_usd END) AS agg_vol_eth_30d,COUNT(*) AS agg_trades_12m,COUNT(DISTINCT CAST(block_date AS DATE)) AS agg_active_days_12mFROM dex_aggregator.tradesWHERE blockchain = 'ethereum'AND block_date >= DATE_ADD('month', -12, CURRENT_DATE)AND taker IN (SELECT wallet FROM aud)GROUP BY taker),eth_tx AS (SELECT "from" AS wallet,CAST(MIN(block_time) AS DATE) AS first_tx_eth,CAST(MAX(block_time) AS DATE) AS last_tx_ethFROM ethereum.transactionsWHERE "from" IN (SELECT wallet FROM aud)GROUP BY "from"),xfer_base AS (SELECT "from" AS wallet, blockchain, amount_usdFROM tokens.transfersWHERE block_date >= DATE_ADD('month', -12, CURRENT_DATE)AND "from" IN (SELECT wallet FROM aud)UNION ALLSELECT "to" AS wallet, blockchain, amount_usdFROM tokens.transfersWHERE block_date >= DATE_ADD('month', -12, CURRENT_DATE)AND "to" IN (SELECT wallet FROM aud)),transfers AS (SELECT wallet,COUNT(DISTINCT blockchain) AS distinct_chains_12m,SUM(CASE WHEN blockchain = 'ethereum' THEN amount_usd END) AS token_vol_eth_12m,SUM(CASE WHEN blockchain <> 'ethereum' THEN amount_usd END) AS token_vol_l2_12mFROM xfer_baseGROUP BY wallet),defi AS (SELECT address AS wallet,SUM(CASE WHEN block_time >= DATE_ADD('month', -12, CURRENT_DATE) THEN amount_usd END) AS defi_dep_12m,SUM(CASE WHEN block_time >= DATE_ADD('day', -30, CURRENT_DATE) THEN amount_usd END) AS defi_dep_30d