select distinct tx_group_id, sender from algorand.application_call_transaction

where app_id = 900653165

and block_timestamp::date > '2022-10-01'

)

,airdrop as(

select t.receiver as airdrop_receiver, max(t.block_timestamp) as airdrop_claim, sum(t.amount) airdrop_amount

from app_call ac

inner join algorand.transfers t on ac.sender = t.receiver and ac.tx_group_id = t.tx_group_id

t.amount > 0

and ac.sender = t.receiver

and t.asset_id = 900652777

group by t.receiver

order by airdrop_amount desc

,gov_stakers as (

select t.asset_sender as sender, sum(amount) as commit_amount

from

algorand.application_call_transaction app

inner join algorand.transfers t on app.tx_group_id = t.tx_group_id

where

app_id = 900653165

and t.asset_id = 900652777

and t.amount > 0

group by t.asset_sender

select airdrop_receiver, airdrop_claim, airdrop_amount, commit_amount, (commit_amount/airdrop_amount)*100 as percent_committed

from airdrop air

left join gov_stakers gov on air.airdrop_receiver = gov.sender

order by airdrop_amount desc