Return to Question

added 183 characters in body

Source Link

edited Jan 14, 2022 at 20:47

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

Any help would be greatly appreciated. Thanks! :)

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

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

Source Link

asked Jan 14, 2022 at 20:22

Kyle M

How to Remove Burned NFTs from SQL Output

Any help would be greatly appreciated. Thanks! :)

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