itsxenuxTop 20 GMT airdrop receivers wallet
    Updated 2022-03-23
    with query_table1 as
    (select tx_id
    from solana.transfers
    where source = 'HhXAKYmRzBNi7BjkDs2fbwJ49mnpWUtzyXEf8PAMArs4'
    and authority = 'STEPNq2UGeGSzCyGVr2nMQAzf8xuejwqebd84wcksCK'
    and date(block_timestamp) >= '2022-03-15'
    ),
    query_table2 as (
    select joined_table1.value:owner as wallet,
    sum(joined_table1.value:uiTokenAmount:uiAmount) as amount
    from solana.transfers
    join table(flatten(input => posttokenbalances )) joined_table1
    where tx_id in (select tx_id from query_table1)
    and wallet != 'STEPNq2UGeGSzCyGVr2nMQAzf8xuejwqebd84wcksCK'
    group by wallet
    order by amount desc limit 20
    ),
    transfer_out as (
    select authority as wallet, sum(instruction:parsed:info:amount)/pow(10,9) as amount
    from solana.transfers
    where date(block_timestamp) >= '2022-03-15'
    AND posttokenbalances[0]:mint='7i5KKsX2weiTkry7jA4ZwSuXGhs5eJBEjY8vVxR4pfRx'
    and authority in (SELECT wallet from query_table2) group by wallet)

    select query_table2.wallet, query_table2.amount as intered_amount, (transfer_out.amount/query_table2.amount) * 100 as out_percentage,
    case when out_percentage >= 100 then 100 else out_percentage end
    as out_percent
    from query_table2
    join transfer_out ON query_table2.wallet = transfer_out.wallet
    Run a query to Download Data