-- forked from tkvresearch / main-moccasin @ https://flipsidecrypto.xyz/tkvresearch/q/YjNHdEn-4jhD/main-moccasin --logic from @oladee/PENGU Airdrop on Dune with claimer as ( select tx_to, sum(amount) as total_claimed from solana.core.fact_transfers where tx_from in ( '3HA76bpwHuST6Uo9BouJ4A5GpAiDuerr7QBenUqbXZAL' ) and mint = '2zMMhcVQEXDtdE6vsFS7S7D5oUodfJHE8vd1gnBouauv' group by 1 ), bal_holding as (select address, sum(amount) as holding_bal from (select tx_to as address, amount from solana.core.fact_transfers where tx_to in (select tx_to from claimer) and mint = '2zMMhcVQEXDtdE6vsFS7S7D5oUodfJHE8vd1gnBouauv' union all select tx_from as address, -amount from solana.core.fact_transfers where tx_from in (select tx_to from claimer) and mint = '2zMMhcVQEXDtdE6vsFS7S7D5oUodfJHE8vd1gnBouauv') group by 1 having holding_bal > 0) select distinct --count(distinct address) as add_cnt, CASE WHEN total_claimed = holding_bal THEN 'Holding All Airdrop' WHEN total_claimed < holding_bal THEN 'Bought More PENGU' WHEN total_claimed > holding_bal THEN CASE WHEN holding_bal < total_claimed * 0.1 THEN 'Sold or Move 90%' WHEN holding_bal < total_claimed * 0.25 THEN 'Sold or Move 75%' WHEN holding_bal < total_claimed * 0.5 THEN 'Sold or Move 50%' ELSE 'Sold or Move 25%' END when holding_bal < 1 then 'Sold or Move All Airdrop' else 'Sold or Move All Airdrop' END AS holding_status, COUNT( CASE WHEN total_claimed = holding_bal THEN 'Holding All Airdrop' WHEN total_claimed < holding_bal THEN 'Bought More PENGU' WHEN total_claimed > holding_bal THEN CASE WHEN holding_bal < total_claimed * 0.1 THEN 'Sold or Move 90%' WHEN holding_bal < total_claimed * 0.25 THEN 'Sold or Move 75%' WHEN holding_bal < total_claimed * 0.5 THEN 'Sold or Move 50%' ELSE 'Sold or Move 25%' END when holding_bal < 1 then 'Sold or Move All Airdrop' else 'Sold or Move All Airdrop' end) OVER (PARTITION BY CASE WHEN total_claimed = holding_bal THEN 'Holding All Airdrop' WHEN total_claimed < holding_bal THEN 'Bought More PENGU' WHEN total_claimed > holding_bal THEN CASE WHEN holding_bal < total_claimed * 0.1 THEN 'Sold or Move 90%' WHEN holding_bal < total_claimed * 0.25 THEN 'Sold or Move 75%' WHEN holding_bal < total_claimed * 0.5 THEN 'Sold or Move 50%' ELSE 'Sold or Move 25%' END when holding_bal < 1 then 'Sold or Move All Airdrop' else 'Sold or Move All Airdrop' end) AS holding_status_cnt from (select b.address, a.total_claimed, b.holding_bal as holding_bal from claimer a left join bal_holding b on a.tx_to = b.address) order by 2 desc

ellerydurwin