helium_bgum_txs as (

select

date(block_timestamp) as date,

tx_id,

program_id,

signers[0] as deployer, -- fee_payer

f.value:accounts[1]::string as collector, --hotspot owner

f.value:accounts[8]::string as collection_address, --hotspot

f.value:accounts[9]::string as collection_metadata

from

solana.core.fact_events e

inner join lateral flatten(input => inner_instruction:instructions) f

where

date(block_timestamp) >= '2023-04-17'

and program_id in (

'hemjuPXBpNvggtaUnN1MwT3wrdhttKEfosTcc2P9Pg8',

'1atrmQs3eq1N2FEYWu6tyTXbCjP4uQwExpjtnhXtS8h'

)

and f.value:programId = 'BGUMAp9Gq7iTEuizy4pqaxsTyUCBK68MDfK752saRPUY'

and succeeded

--and tx_id = '5HE6C7xX8gs8J2kufs36MK6sTTit4VEZePzeSzmaRuwcgfnoofGooi4Mc7FtxjRCF3VwAfbx1HAtojBDeEsJTvsV'

--limit 10

)

-- signer,

-- program_id

-- date,

count(distinct collector) as unique_collectors,

count(distinct collection_address) as unique_collections,

count(1) as total_mints

helium_bgum_txs