I am querying the Cardano Postgres DB and can't figure out how to remove NFTs (multi-assets) that have been burned. In the SQL query below, I am selecting the mint transactions for an asset fingerprint that I know to be a duplicate (meaning there was a burn associated with it). The SQL output is below.
After doing some manual research I was able to figure out that the 3rd row in the output is a mint transaction that 'burned' the 1st row's mint transaction (Hence the -1 quantity). The 2nd row is the NFT that is active today.
However, I am unsure how to know the 3rd transaction burned the 1st row and not the 2nd row. I would like to have my SQL script automatically remove the 1st row and only show active (not burned) NFTs.
Any help would be greatly appreciated. Thanks! :)
P.S. Here are the same mint transactions on Cardano scan: https://cardanoscan.io/tokenminttransactions?assetId=b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b949444503030383436
with policy_assets as (
id as ma_id
, encode(policy, 'hex') as policy_id
where encode(policy, 'hex') = 'b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b9494'
order by id
-- Dupe asset fingerprint
from ma_tx_mint m
inner join policy_assets p
on m.ident = p.ma_id
where fingerprint in ('asset1x0hrrjs6xtmhnwy8ee60j2jrn4k005awqum4zd')
order by m.id