user1011: 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



user6431: 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.

1011: <p>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.</p>
<p>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.</p>
<p>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.</p>
<p>Any help would be greatly appreciated. Thanks! :)</p>
<p>P.S. Here are the same mint transactions on Cardano scan: <a href="https://cardanoscan.io/tokenminttransactions?assetId=b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b949444503030383436" rel="nofollow noreferrer">https://cardanoscan.io/tokenminttransactions?assetId=b92f6473f18d4b78733d022fd89f3cacc1484fab6eddfd3c5d4b949444503030383436</a></p>
<pre><code>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
</code></pre>
<p><a href="https://i.stack.imgur.com/w0n71.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/w0n71.png" alt="SQL Output" /></a></p>
 6431: <p>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.</p>
<pre><code>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;
</code></pre>
<p>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.</p>
