select tx_id

from solana.transfers

where block_timestamp::date >= '2022-03-15'

and source = 'HhXAKYmRzBNi7BjkDs2fbwJ49mnpWUtzyXEf8PAMArs4'

and authority = 'STEPNq2UGeGSzCyGVr2nMQAzf8xuejwqebd84wcksCK'

),

wallet_tbl as (

select

f.value:owner as wallet,

sum(f.value:uiTokenAmount:uiAmount) as amount

from solana.transfers

left join table(flatten(input => posttokenbalances )) f

where tx_id in (select* from trx_tbl)

and wallet != 'STEPNq2UGeGSzCyGVr2nMQAzf8xuejwqebd84wcksCK'

group by wallet

order by amount DESC

limit 50

),

transfer_out as (

select authority as wallet, sum(instruction:parsed:info:amount::float/1e9) as amount

from solana.transfers

where posttokenbalances[0]:mint='7i5KKsX2weiTkry7jA4ZwSuXGhs5eJBEjY8vVxR4pfRx'

AND authority in (SELECT wallet from wallet_tbl)

and block_timestamp::date >= '2022-03-15'

group by wallet

SELECT i.wallet, i.amount as in_amount, ZEROIFNULL(o.amount) as out_amount , (o.amount/i.amount) * 100 as out_percent,

case when out_percent >100 then 100 else out_percent end as out_percent_correction

from wallet_tbl i

LEFT join transfer_out o on o.wallet = i.wallet