SELECT

event_data:id AS nft_id,

event_data:to AS wallet_address -- Include the wallet address to track where the NFT was deposited

FROM flow.core.fact_events

WHERE event_contract = 'A.edf9df96c92f4595.Pinnacle'

AND event_type = 'Deposit'

AND event_data:to = '0xedf9df96c92f4595' -- Filter for the specific vault address

),

Withdrawals AS (

SELECT

event_data:id AS nft_id,

event_data:from AS wallet_address -- Include the wallet address to track where the NFT was withdrawn from

FROM flow.core.fact_events

WHERE event_contract = 'A.edf9df96c92f4595.Pinnacle'

AND event_type = 'Withdraw'

AND event_data:from = '0xedf9df96c92f4595' -- Filter for the specific vault address

),

Minted AS (

SELECT

event_data:id AS nft_id,

CAST(event_data:editionID AS INT) AS edition_id -- Cast editionID to integer for easier manipulation

FROM flow.core.fact_events

WHERE event_contract = 'A.edf9df96c92f4595.Pinnacle'

AND event_type = 'PinNFTMinted'

SELECT

'0xedf9df96c92f4595' AS vault_address, -- Explicitly include the vault address in the final output

m.edition_id AS editionID,

COUNT(m.edition_id) AS quantity

FROM Deposits d

LEFT JOIN Minted m ON d.nft_id = m.nft_id

WHERE NOT EXISTS (

SELECT 1

FROM Withdrawals w