WITH mx AS ( SELECT DATEADD('hours', -7, MAX(block_timestamp)) AS recency FROM solana.core.fact_transactions WHERE block_timestamp >= CURRENT_DATE - 1 ), b0 AS ( SELECT tx_id , ARRAY_SIZE(account_keys) AS ak_sz , ARRAY_SIZE(instructions[0]:accounts) AS in_ac_sz , instructions[0]:accounts AS in_ac , signers[0]::string AS address , date_trunc('hour', DATEADD('hours', -7, block_timestamp)) AS hour , DATEADD('hours', -7, block_timestamp)::date AS date , ARRAY_SIZE(inner_instructions[0]:instructions) AS sz , inner_instructions[0]:instructions AS i , i[0]:parsed:info AS i0 , i[1]:parsed:info AS i1 , i[2]:parsed:info AS i2 , i[3]:parsed:info AS i3 , i[4]:parsed:info AS i4 , i[5]:parsed:info AS i5 , i[6]:parsed:info AS i6 , i[0]:parsed:type::string AS type0 , i[1]:parsed:type::string AS type1 , i[2]:parsed:type::string AS type2 , i[3]:parsed:type::string AS type3 , COALESCE(i0:authority, i0:source)::string AS address0 , i0:mint::string AS mint0 , i1:mint::string AS mint1 , i2:mint::string AS mint2 , i3:mint::string AS mint3 , i4:mint::string AS mint4 , i5:mint::string AS mint5 , COALESCE(i0:amount, i0:lamports)::int AS amt0_raw , COALESCE(i1:amount, i1:lamports)::int AS amt1_raw , COALESCE(i2:amount, i2:lamports)::int AS amt2_raw , COALESCE(i3:amount, i3:lamports)::int AS amt3_raw , CASE WHEN (sz = 6 AND type2 = 'mintTo') OR (sz=8 AND type2 = 'assign' ) THEN 'Protocol Initialize Pool' -- 14 WHEN type0 = 'allocate' AND type1 = 'assign' AND type2 = 'mintTo' AND in_ac[1] = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Protocol Initialize Pool' WHEN (sz = 6 AND type2 = 'burn') OR (sz=9 AND type2 = 'sol-transfer') THEN 'Protocol Burn Pool' -- 14 WHEN type0 = 'transfer' AND type1 = 'transfer' AND type2 = 'burn' AND in_ac[1] = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Protocol Burn Pool' WHEN type0 = 'burn' AND type1 = 'transfer' AND type2 = 'transfer' AND amt2_raw IS NOT NULL THEN 'User Sell - Pregame' -- 12 WHEN type0 = 'burn' AND type1 = 'transfer' AND amt0_raw > 0 AND amt1_raw > 0 THEN 'User Sell - Win' -- 12 WHEN type0 = 'burn' AND type1 = 'transfer' AND amt0_raw > 0 AND amt1_raw = 0 THEN 'User Sell - Loss' -- 12 WHEN type0 = 'transfer' AND type1 = 'transfer' AND (type2 = 'mintTo' OR type3 = 'mintTo') THEN 'User Buy - Pregame' ELSE 'Other' END AS tx_type , CASE WHEN in_ac_sz = 15 THEN in_ac[6] WHEN in_ac_sz = 18 THEN in_ac[7] WHEN in_ac_sz = 20 AND tx_type = 'User Buy - Pregame' THEN in_ac[7] WHEN in_ac_sz = 23 AND tx_type = 'User Buy - Pregame' THEN in_ac[9] WHEN in_ac_sz = 20 AND tx_type = 'User Sell - Pregame' THEN in_ac[7] WHEN in_ac_sz = 19 AND tx_type = 'Protocol Burn Pool' THEN in_ac[8] WHEN in_ac_sz = 21 AND tx_type = 'Protocol Initialize Pool' THEN in_ac[6] WHEN in_ac_sz = 26 AND tx_type = 'Protocol Initialize Pool' THEN in_ac[8] WHEN in_ac_sz = 21 THEN in_ac[9] ELSE 'Other' END AS token , CASE WHEN token = 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX' THEN 6 ELSE 9 END AS decimals , amt0_raw * POWER(10, -decimals) AS amt0 , amt1_raw * POWER(10, -decimals) AS amt1 , amt2_raw * POWER(10, -decimals) AS amt2 , amt3_raw * POWER(10, -decimals) AS amt3 , CASE WHEN i0:destination::string = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Props' WHEN i0:destination::string = 'BetZfJzZaTon6bRAupYKMgt3LsS8NyKgQBhGoA8kWxnP' THEN 'Bet3' ELSE 'Other' END AS destination0 , CASE WHEN i1:destination::string = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Props' WHEN i1:destination::string = 'BetZfJzZaTon6bRAupYKMgt3LsS8NyKgQBhGoA8kWxnP' THEN 'Bet3' ELSE 'Other' END AS destination1 , CASE WHEN i2:destination::string = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Props' WHEN i2:destination::string = 'BetZfJzZaTon6bRAupYKMgt3LsS8NyKgQBhGoA8kWxnP' THEN 'Bet3' ELSE 'Other' END AS destination2 , CASE WHEN i3:destination::string = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Props' WHEN i3:destination::string = 'BetZfJzZaTon6bRAupYKMgt3LsS8NyKgQBhGoA8kWxnP' THEN 'Bet3' ELSE 'Other' END AS destination3 , CASE WHEN i4:destination::string = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Props' WHEN i4:destination::string = 'BetZfJzZaTon6bRAupYKMgt3LsS8NyKgQBhGoA8kWxnP' THEN 'Bet3' ELSE 'Other' END AS destination4 , CASE WHEN i5:destination::string = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Props' WHEN i5:destination::string = 'BetZfJzZaTon6bRAupYKMgt3LsS8NyKgQBhGoA8kWxnP' THEN 'Bet3' ELSE 'Other' END AS destination5 , CASE WHEN i6:destination::string = '6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk' THEN 'Props' WHEN i6:destination::string = 'BetZfJzZaTon6bRAupYKMgt3LsS8NyKgQBhGoA8kWxnP' THEN 'Bet3' ELSE 'Other' END AS destination6 , CASE WHEN tx_type = 'User Buy - Pregame' THEN mint2 WHEN tx_type = 'User Sell - Pregame' THEN mint0 WHEN tx_type = 'User Sell - Win' THEN mint0 WHEN tx_type = 'User Sell - Loss' THEN mint0 ELSE mint0 END AS team_token , CASE WHEN destination0 = 'Bet3' OR destination1 = 'Bet3' OR destination2 = 'Bet3' OR destination3 = 'Bet3' OR destination4 = 'Bet3' OR destination5 = 'Bet3' OR destination6 = 'Bet3' THEN 'Bet3' ELSE 'Props' END AS src -- , * FROM solana.core.fact_transactions WHERE block_timestamp >= CURRENT_DATE - 90 AND succeeded AND instructions[0]:programId = 'GrcZwT9hSByY1QrUTaRPp6zs5KxAA5QYuqEhjT1wihbm' AND NOT address IN ('6Ch8KyKpkZHKHNzEioLYg6b3w1WM8NMYg4pYnVsBU3pk','AoNVE2rKCE2YNA44V7NQt8N73JdPM7b6acZ2vzSpyPyi','9VhsSZ6ni7dZtmKRHE81yAd3UQW1oKu9LNEWRGFMA5wj') AND tx_type LIKE '%User%' ), p0 AS ( SELECT *, close as price , recorded_hour::date AS date , ROW_NUMBER() OVER (PARTITION BY symbol, date ORDER BY recorded_hour DESC) AS rn_day , ROW_NUMBER() OVER (PARTITION BY symbol, DATE_TRUNC('week', date) ORDER BY recorded_hour DESC) AS rn_week , ROW_NUMBER() OVER (PARTITION BY symbol, DATE_TRUNC('month', date) ORDER BY recorded_hour DESC) AS rn_month , ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY recorded_hour DESC) AS rn_all FROM solana.core.fact_token_prices_hourly WHERE recorded_hour >= CURRENT_DATE - 90 AND symbol IN ('SOL','USDH','MSOL') ), prices AS ( SELECT CASE WHEN SYMBOL = 'USDH' THEN 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX' WHEN SYMBOL = 'MSOL' THEN 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So' ELSE 'So11111111111111111111111111111111111111112' END AS token , date , price , rn_day , rn_week , rn_month , rn_all FROM p0 WHERE LEAST(rn_day, rn_week, rn_month, rn_all) = 1 ), b1 AS ( SELECT tx_id ,address , b.date as date1 , 1 AS n_users , SUM(CASE WHEN tx_type LIKE '%Pregame' THEN 1 ELSE 0 END) AS n_bets , SUM(CASE WHEN tx_type = 'User Sell - Win' AND b.token = 'So11111111111111111111111111111111111111112' THEN amt1 ELSE 0 END) AS payout_sol , SUM(CASE WHEN tx_type = 'User Sell - Win' AND b.token = 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX' THEN amt1 ELSE 0 END) AS payout_usdh , SUM(CASE WHEN tx_type = 'User Sell - Win' AND b.token = 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So' THEN amt1 ELSE 0 END) AS payout_msol , SUM(CASE WHEN tx_type = 'User Sell - Win' THEN amt1 ELSE 0 END * COALESCE(pd.price, pw.price, pm.price, pa.price)) AS payout_usd , SUM(CASE WHEN tx_type = 'User Buy - Pregame' THEN amt2 WHEN tx_type = 'User Sell - Pregame' THEN amt0 ELSE 0 END) AS teamTokenAmount , SUM( CASE WHEN b.token <> 'So11111111111111111111111111111111111111112' THEN 0 WHEN tx_type = 'User Buy - Pregame' THEN amt0 + amt1 WHEN tx_type = 'User Sell - Pregame' THEN amt2 + amt1 ELSE 0 END ) AS sol_volume , SUM( CASE WHEN b.token <> 'USDH1SM1ojwWUga67PGrgFWUHibbjqMvuMaDkRJTgkX' THEN 0 WHEN tx_type = 'User Buy - Pregame' THEN amt0 + amt1 WHEN tx_type = 'User Sell - Pregame' THEN amt2 + amt1 ELSE 0 END ) AS usdh_volume , SUM( CASE WHEN b.token <> 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So' THEN 0 WHEN tx_type = 'User Buy - Pregame' THEN amt0 + amt1 WHEN tx_type = 'User Sell - Pregame' THEN amt2 + amt1 ELSE 0 END ) AS msol_volume , SUM( CASE WHEN tx_type = 'User Buy - Pregame' THEN (amt0 + amt1) * COALESCE(pd.price, pw.price, pm.price, pa.price, 0) WHEN tx_type = 'User Sell - Pregame' THEN (amt2 + amt1) * COALESCE(pd.price, pw.price, pm.price, pa.price, 0) ELSE 0 END ) AS usd_volume , SUM( CASE WHEN tx_type = 'User Buy - Pregame' THEN amt1 * COALESCE(pd.price, pw.price, pm.price, pa.price, 0) WHEN tx_type = 'User Sell - Pregame' THEN amt2 * COALESCE(pd.price, pw.price, pm.price, pa.price, 0) ELSE 0 END ) AS usd_fees , SUM( CASE WHEN b.token <> 'So11111111111111111111111111111111111111112' THEN 0 WHEN tx_type = 'User Buy - Pregame' THEN amt1 WHEN tx_type = 'User Sell - Pregame' THEN amt2 ELSE 0 END ) AS sol_fees , SUM( CASE WHEN tx_type = 'User Buy - Pregame' THEN amt1 WHEN tx_type = 'User Sell - Pregame' THEN amt2 ELSE 0 END * COALESCE(pd.price, pw.price, pm.price, pa.price) ) AS fees_usd FROM b0 b LEFT JOIN prices pd ON pd.token = b.token AND b.date = pd.date AND pd.rn_day = 1 LEFT JOIN prices pw ON pw.token = b.token AND DATE_TRUNC('week', b.date) = DATE_TRUNC('week', pw.date) AND pw.rn_week = 1 LEFT JOIN prices pm ON pm.token = b.token AND DATE_TRUNC('month', b.date) = DATE_TRUNC('month', pm.date) AND pm.rn_month = 1 LEFT JOIN prices pa ON pa.token = b.token AND pa.rn_all = 1 GROUP BY 1, 2, 3 ), b2 AS ( SELECT address , MIN(date1) AS first_date FROM b1 GROUP BY 1 ) SELECT CASE WHEN bet_volume < 10 THEN 'Below $10 Bet' WHEN bet_volume < 50 THEN 'Between $10 and $50 Bet' WHEN bet_volume < 100 THEN 'Between $50 and $100 Bet' WHEN bet_volume < 500 THEN 'Between $100 and $500 Bet' WHEN bet_volume < 1000 THEN 'Between $500 and $1,000 Bet' WHEN bet_volume < 2000 THEN 'Between $1,000 and $2,000 Bet' WHEN bet_volume < 5000 THEN 'Between $2,000 and $5,000 Bet' ELSE 'Over $5,000 Bet' END as bet_size, count(*) as total_bets, sum(bet_volume) as total_bet_volume FROM ( SELECT tx_id, sum(usd_volume) as bet_volume FROM b1 where not n_bets = 0 AND address LIKE '{{wallet}}' group by 1 ) GROUP BY 1

Pool Props: Decentralized Sports Exchange