Query editor
-- Collector Crypt CARDS buybacks-- Wallet: 3nGNwiz1qevPjhEoQi1dLj16oTkjmbevTnpV9piWY7Kq-- A tx counts as a buyback when USDC leaves the wallet AND CARDS arrives in the same txWITH tx_flows AS (SELECTtx_id,MIN(block_time) AS block_time,-- USDC out, netted against any USDC returned in the same tx (router refunds).-- CAST to double because raw amount is uint256 and unsigned subtraction underflows.( SUM(CASE WHEN token_mint_address = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'AND from_owner = '3nGNwiz1qevPjhEoQi1dLj16oTkjmbevTnpV9piWY7Kq'THEN CAST(amount AS double) ELSE 0 END)- SUM(CASE WHEN token_mint_address = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'AND to_owner = '3nGNwiz1qevPjhEoQi1dLj16oTkjmbevTnpV9piWY7Kq'THEN CAST(amount AS double) ELSE 0 END)) / 1e6 AS usdc_spent,-- CARDS in: use amount_display if Dune metadata knows the token,-- otherwise fall back to raw amount at 6 decimalsSUM(CASE WHEN token_mint_address = 'CARDSccUMFKoPRZxt5vt3ksUbxEFEcnZ3H2pd3dKxYjp'AND to_owner = '3nGNwiz1qevPjhEoQi1dLj16oTkjmbevTnpV9piWY7Kq'THEN COALESCE(amount_display, CAST(amount AS double) / 1e6) ELSE 0 END) AS cards_boughtFROM tokens_solana.transfersWHERE (from_owner = '3nGNwiz1qevPjhEoQi1dLj16oTkjmbevTnpV9piWY7Kq'OR to_owner = '3nGNwiz1qevPjhEoQi1dLj16oTkjmbevTnpV9piWY7Kq')AND token_mint_address IN ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v', -- USDC'CARDSccUMFKoPRZxt5vt3ksUbxEFEcnZ3H2pd3dKxYjp' -- CARDS)AND block_date >= DATE '2025-09-01'GROUP BY tx_id),buybacks AS (SELECTDATE_TRUNC('day', block_time) AS period,usdc_spent,cards_boughtFROM tx_flowsWHERE usdc_spent > 0AND cards_bought > 0)SELECTperiod AS day,ROUND(SUM(usdc_spent), 2) AS usdc_spent,ROUND(SUM(cards_bought), 2) AS cards_bought,ROUND(SUM(usdc_spent) / NULLIF(SUM(cards_bought), 0), 6) AS avg_price,ROUND(SUM(SUM(usdc_spent)) OVER (ORDER BY period), 2) AS cumulative_usdc,ROUND(SUM(SUM(cards_bought)) OVER (ORDER BY period), 2) AS cumulative_cards,ROUND(SUM(SUM(usdc_spent)) OVER (ORDER BY period)/ NULLIF(SUM(SUM(cards_bought)) OVER (ORDER BY period), 0), 6) AS cumulative_avg_priceFROM buybacksGROUP BY periodORDER BY period DESC