duneanalytics / spellbook

SQL views for Dune
Other
1.18k stars 1.12k forks source link

[BUG] token_mint_address in tokens_solana.transfers is different on solscan #6690

Open 0xroll opened 2 months ago

0xroll commented 2 months ago

Description

token_mint_address in tokens_solana.transfers is different on solscan .

some examples in the discord thread https://discord.com/channels/757637422384283659/1281025587405787177/1282371830186774651

Current behavior

token_mint_address showing a different address

Expected behavior

addresses should match with solana explorers

Impacted model(s)

https://github.com/duneanalytics/spellbook/blob/main/dbt_subprojects/solana/models/tokens/solana/tokens_solana_transfers.sql#L66

Possible solution

havnt thought of a solution yet, just creating this issue to track

tentabs00 commented 1 month ago

here are two specific transactions with a different dune token address than what solscan shows:

Transaction 1

Transaction 2

Query to retrieve negative balances

here is a dune query showing all negative balances for the MNDE solana token, you can remove the token filter in the first CTE to assess all tokens

-- filtering to only a set of tokens to improve performance
-- you can remove this to see the results for all tokens
with transfers_filtered as (
    select *
    from tokens_solana.transfers t
    where t.token_mint_address in (
        'MNDEFzGvMt87ueuHvVU9VcTqsAP5b3fTGPsHuuPA5ey'
    )
),
-- aggregate wallet-level transfers by counting transfers in as positive and transfers out as negative
wallet_transfers as (
    select 'solana' as chain
    ,t.block_time
    ,t.from_token_account as wallet_address
    ,-t.amount as transfer_amount
    ,token_mint_address
    from transfers_filtered t

    union all

    select 'solana' as chain
    ,t.block_time
    ,t.to_token_account as wallet_address
    ,t.amount as transfer_amount
    ,token_mint_address
    from transfers_filtered t
),

-- calculate wallet balances by summing net transfers over time for each wallet
wallet_balances as (
    select chain
    ,block_time
    ,wallet_address
    ,token_mint_address
    ,transfer_amount
    ,sum(transfer_amount) over (partition by chain,wallet_address,token_mint_address order by block_time asc) as balance
    from wallet_transfers
)

select chain
,token_mint_address
,wallet_address
,min(balance) as lowest_balance
from wallet_balances

-- filter to balances below -1 rather than 0 to filter any rounding errors
where balance < -1
group by 1,2,3
order by 1,2,4 desc