select

instruction :accounts[6] ::string as mint

from solana.core.fact_events

where block_timestamp > '2022-11-04'

and program_id = 'Guard1JwRhJkVH6XZhzoYxeBVQe872VH6QggF4BWmS9g' -- Candy Guard Program ID

and instruction :accounts[0] = 'DXrMes8iT45Nga2G66f7Yyxy2iRd9TxoC8sPt5g1p98p' -- Candy Guard

and instruction :accounts[1] = 'CndyV3LdqHUfDLmE5naZjVN8rBZz4tqhdefbAnjHG3JR'

and instruction :accounts[2] = '6A9aRFG7KirCpYJFFcNRetKsFLTxC221zaWZd8rYCLqe' -- Candy Machine

and instruction :accounts[3] = 'A4FM6h8T5Fmh9z2g3fKUrKfZn6BNFEgByR8QGpdbQhk1' -- Candy Machine Authority

and instruction :accounts[9] = 'wuFBfSJFb6TvSk8rZBhTxjp2BbMdwqYP2LhB8eVaxJP' -- Collection Authority

and instruction :accounts[10] = '4mKSoDDqApmF1DqXvVTSL6tu2zixrSSNjqMxUnwvVzy2' -- Collection Mint

), DeGods as (

SELECT

DISTINCT mint

FROM solana.core.dim_nft_metadata

WHERE PROJECT_NAME LIKE 'DeGods'

), tab0 as (

SELECT

mint,

max(block_timestamp) as max_day

FROM solana.core.fact_transfers

WHERE mint in (SELECT mint from y00ts)

OR mint in (SELECT mint FROM DeGods)

GROUP BY 1

), tab02 as (

SELECT

tx_to,

sum(CASE WHEN a.mint IN (SELECT * FROM y00ts) THEN 1 ELSE 0 END) as y00ts_holding,

sum(CASE WHEN a.mint IN (SELECT * FROM DeGods) THEN 1 ELSE 0 END) as DeGods_holding,

COUNT(*) as Dust_labs_NFTs

FROM solana.core.fact_transfers a

LEFT outer JOIN tab0

ON tab0.mint = a.mint

AND max_day = block_timestamp

WHERE NOT tab0.mint IS NULL