How to Remove Burned NFTs from SQL Output

Question

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 (
    select
        id as ma_id
        , encode(policy, 'hex') as policy_id
        , name
        , fingerprint
    from multi_asset
    where encode(policy, 'hex') = 'b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b9494'
    order by id
)

-- Dupe asset fingerprint
select *
from ma_tx_mint m
inner join policy_assets p
    on m.ident = p.ma_id
where fingerprint in ('asset1x0hrrjs6xtmhnwy8ee60j2jrn4k005awqum4zd')
order by m.id

Answer 1

You need to join all the assets for a policy to their transactions and join the transaction addresses to stake addresses. If the most recent transaction has a null stake address that means it's been burned.

select
  a.nft_name,
  sa.view
from
  (
    --selecting (nfts x tx) grouped by most recent tx
    select
      nft_name,
      max(tx_id) as tx_id
    from
      (
        --entire raw set
        select
          convert_from(
            concat('\x', encode(asset.name, 'hex')) :: bytea,
            'UTF8'
          ) as nft_name,
          sa.view as stake_address,
          tx.id as tx_id
        from
          ma_tx_out matx
          inner join multi_asset asset on matx.ident = asset.id
          inner join tx_out tx on tx.id = matx.tx_out_id
          left outer join stake_address as sa on tx.stake_address_id = sa.id
        where
          asset.policy = decode('SOME_POLICY_ID', 'hex') 
      ) a
    group by
      nft_name
  ) a
  inner join tx_out tx on tx.id = a.tx_id
  inner join stake_address as sa on tx.stake_address_id = sa.id
order by
  a.nft_name;

The left join to stake address inside the inner query will provide your records where the max tx_id is associated with a null stake id, and the outermost inner join to stake address will filter that result out of the return set.

Answer 2

All tables (other than the epoch table) are designed to be append only (ignoring rollbacks). That means that you need to modify your SQL query to filter out the items that you do not want to see.