WITH tab1 AS (

SELECT

DISTINCT tx_id

FROM solana.core.fact_events

WHERE PROGRAM_ID LIKE '6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P'

AND block_timestamp > '2024-05-10'

SELECT

date(block_timestamp) as date,

sum(case when ARRAY_TO_STRING(log_messages, ', ') LIKE '%Create%' then ((pre_balances[0] - post_balances[0]) / POWER(10, 9)) - 0.02

else ((pre_balances[0] - post_balances[0]) / POWER(10, 9)) end) as volume_sol,

count(DISTINCT tx_id) as events,

count(DISTINCT signers) as users

FROM solana.core.fact_transactions

WHERE tx_id IN (SELECT * FROM tab1)

AND block_timestamp > '2024-05-10'

AND ARRAY_TO_STRING(log_messages, ', ') LIKE '%Buy%'

AND SUCCEEDED

GROUP BY 1