Query editor
WITH token_map AS (SELECT token_address, launchpad FROM query_7979183WHERE launchpad <> 'other' AND first_date >= DATE '2026-07-01'),launches AS (SELECT launchpad, COUNT(DISTINCT token_address) AS tokens_launchedFROM query_7979183 WHERE launchpad <> 'other' AND first_date >= DATE '2026-07-01' GROUP BY 1),stock AS (SELECT DISTINCT address AS stock_token FROM robinhood.creation_tracesWHERE "from" = 0x4783C67b63dE2B358Ac5951a7D41F47A38F3C046 AND block_time >= TIMESTAMP '2026-01-01'),base AS (SELECT block_time, block_date, tx_hash, tx_from, taker, token_bought_address, token_sold_address, token_bought_amount, token_sold_amount, amount_usdFROM dex.trades WHERE blockchain='robinhood' AND block_date >= DATE '2026-07-01'UNION ALLSELECT block_time, block_date, tx_hash, tx_from, taker, token_bought_address, token_sold_address, CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), amount_usdFROM query_7986129 WHERE block_date >= DATE '2026-07-01'),stock_px AS (SELECT block_date,CASE WHEN sb.stock_token IS NOT NULL THEN b.token_bought_address ELSE b.token_sold_address END AS stock_token,SUM(b.amount_usd)/NULLIF(SUM(CASE WHEN sb.stock_token IS NOT NULL THEN b.token_bought_amount ELSE b.token_sold_amount END),0) AS pxFROM base b LEFT JOIN stock sb ON b.token_bought_address=sb.stock_token LEFT JOIN stock ss ON b.token_sold_address=ss.stock_tokenWHERE b.amount_usd>0 AND ((sb.stock_token IS NOT NULL) <> (ss.stock_token IS NOT NULL)) GROUP BY 1,2),relevant AS (SELECT b.block_date, b.block_time, b.tx_hash, b.tx_from, b.taker, b.amount_usd, b.token_bought_address,COALESCE(mb.launchpad, ms.launchpad) AS launchpad,CASE WHEN mb.launchpad IS NOT NULL THEN b.token_bought_address ELSE b.token_sold_address END AS lp_token,CASE WHEN mb.launchpad IS NOT NULL THEN b.token_sold_address ELSE b.token_bought_address END AS counter,CASE WHEN mb.launchpad IS NOT NULL THEN b.token_sold_amount ELSE b.token_bought_amount END AS counter_amountFROM base b LEFT JOIN token_map mb ON b.token_bought_address=mb.token_address LEFT JOIN token_map ms ON b.token_sold_address=ms.token_addressWHERE COALESCE(mb.launchpad, ms.launchpad) IS NOT NULL),ws AS (SELECT block_date, tx_from AS wallet, SUM(COALESCE(amount_usd,0)) vol, COUNT(*)/CAST(COUNT(DISTINCT tx_hash) AS DOUBLE) legs,COUNT(DISTINCT DATE_TRUNC('hour', block_time)) hrs, COUNT(DISTINCT token_bought_address) toksFROM relevant GROUP BY 1,2),flagged AS (SELECT block_date, wallet FROM ws WHERE legs>5 OR (hrs>=20 AND toks<=5 AND vol>1000000)),ct AS (SELECT r.tx_from, r.launchpad, r.lp_token,CASE WHEN sc.stock_token IS NOT NULL THEN 1 ELSE 0 END AS is_rwa,COALESCE(NULLIF(r.amount_usd,0), r.counter_amount*sp.px) AS usd_valueFROM relevant rLEFT JOIN flagged fl ON r.tx_from=fl.wallet AND r.block_date=fl.block_dateLEFT JOIN stock sc ON r.counter=sc.stock_tokenLEFT JOIN stock_px sp ON r.counter=sp.stock_token AND r.block_date=sp.block_dateWHERE fl.wallet IS NULL AND r.tx_from<>0x1925f52cea3bb3e1b4958dad50346b3c34a98b44 AND r.taker<>0x1925f52cea3bb3e1b4958dad50346b3c34a98b44),lp_agg AS (SELECT launchpad, COUNT(*) trades, SUM(usd_value) volume_usd, COUNT(DISTINCT tx_from) traders,SUM(CASE WHEN is_rwa=1 THEN usd_value ELSE 0 END) volume_rwa,COUNT(DISTINCT CASE WHEN is_rwa=1 THEN tx_from END) traders_rwaFROM ct GROUP BY 1),per_token AS (SELECT launchpad, lp_token, COUNT(*) n, SUM(is_rwa) n_rwa FROM ct GROUP BY 1,2),token_rwa AS (SELECT launchpad, COUNT(*) tokens_traded, COUNT(CASE WHEN n_rwa>=0.5*n THEN 1 END) tokens_rwa_paired FROM per_token GROUP BY 1)SELECT la.launchpad, la.tokens_launched, tr.tokens_traded, tr.tokens_rwa_paired,