SELECT

EVENT_DATA:nftId::STRING AS nft_id,

EVENT_DATA:templateId::STRING AS template_id

FROM flow.core.fact_events

WHERE EVENT_CONTRACT LIKE '%AFL%'

AND EVENT_TYPE = 'NFTMinted'

AND TX_SUCCEEDED = 'TRUE'

),

nft_transfers AS (

SELECT

m.template_id,

t.nft_id,

t.to_address,

t.from_address,

ROW_NUMBER() OVER (PARTITION BY t.nft_id ORDER BY t.BLOCK_TIMESTAMP DESC) AS rn

FROM (

SELECT

EVENT_DATA:id::STRING AS nft_id,

EVENT_DATA:to::STRING AS to_address,

EVENT_DATA:from::STRING AS from_address,

BLOCK_TIMESTAMP

FROM flow.core.fact_events

WHERE EVENT_CONTRACT LIKE '%AFL%'

AND EVENT_TYPE IN ('Withdraw', 'Deposit')

AND TX_SUCCEEDED = 'TRUE'

) t

JOIN minted_nfts m ON t.nft_id = m.nft_id

),

final_holdings AS (

SELECT

to_address AS address,

template_id,

COUNT(*) as holding_count