with mint_tx as (

SELECT

block_timestamp,

instruction :accounts [5] as minter,

instruction :accounts [6] as mint,

-- these aren't necessary:

-- instruction:accounts[13] as collection_mint,

-- instruction:accounts[16] as update_authority,

tx_id

FROM

solana.core.fact_events

where

succeeded

and program_id = 'CSGrdwbJ5z58tLGKjjcmiNMj8bG1Zazthk3cXMrbSZoX'

and block_timestamp between '2023-06-29 18:00:00.000'

and '2023-06-30 18:00:00.000'

and instruction :accounts [13] = '86GBq99Qiuq5ZRRcq33QRBT5sM8bTUAjz7owh2BaQCRH'

and array_size(inner_instruction :instructions) > 1

order by

block_timestamp desc

),

mint_agg as (

select

minter,

count(tx_id) as tickets_purchased

from

mint_tx

group by

minter

),

refund_tx as (

select

*

FROM

solana.core.fact_transfers