-- forked from Distribution of $ARENA Airdrop Recipients By Holding Time @ https://flipsidecrypto.xyz/studio/queries/d52a33fc-b2cc-4d5f-a7f0-1a6d44377309 with claimerst as ( select block_timestamp as claim_date, to_address as claimer, amount as Claimed_Amount from avalanche.core.ez_token_transfers where contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c' and from_address = '0x30f587903ef022774f5e35151e0e73c196bd755d' and block_timestamp > '2024-12-29 23:52:50.000' and origin_function_signature = '0x3b439351'), staket as ( select origin_from_address, sum (decoded_log:value/1e18) as Staked_Amount from avalanche.core.ez_decoded_event_logs t1 join claimerst t2 on t1.origin_from_address = t2.claimer and t1.decoded_log:value/1e18 <= t2.claimed_amount and t1.block_timestamp > t2.claim_date where origin_to_address = '0xeffb809d99142ce3b51c1796c096f5b01b4aaec4' and event_name = 'Transfer' and origin_function_signature = '0xb6b55f25' and contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c' and origin_from_address = decoded_log:from group by 1), unstaket as ( select origin_from_address, sum (decoded_log:value/1e18) as UnStaked_Amount from avalanche.core.ez_decoded_event_logs t1 join claimerst t2 on t1.origin_from_address = t2.claimer and t1.decoded_log:value/1e18 <= t2.claimed_amount and t1.block_timestamp > t2.claim_date where origin_to_address = '0xeffb809d99142ce3b51c1796c096f5b01b4aaec4' and event_name = 'Transfer' and origin_function_signature = '0x2e1a7d4d' and contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c' and origin_from_address = decoded_log:to group by 1), netstaket as ( select t1.origin_from_address, sum (staked_amount - unstaked_amount) as Net_Staked_Amount from staket t1 join unstaket t2 on t1.origin_from_address = t2.origin_from_address group by 1), sendt as ( select from_address, sum (amount) as sent_volume, min (block_timestamp) as mindate from avalanche.core.ez_token_transfers t1 join claimerst t2 on t1.from_address = t2.claimer and t1.block_timestamp > t2.claim_date and t1.amount <= t2.claimed_amount where contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c' and from_address not in (select distinct origin_from_address from staket) group by 1), maintable as ( select coalesce (claimer,from_address) as Wallet_address, case when claimer in (select distinct origin_from_address from staket) then '-2' else coalesce (datediff (hour,claim_date,mindate),-1) end as Holding_Hours from claimerst t1 full outer join sendt t2 on t1.claimer = t2.from_address and t1.claim_date < t2.mindate) select case when holding_hours >= 0 and holding_hours <= 24 then '< 24 Hours' when holding_hours > 24 and holding_hours <= 72 then '1 - 3 Days' when holding_hours > 72 and holding_hours <= 168 then '3 - 7 Days' when holding_hours > 168 and holding_hours <= 336 then '7 - 14 Days' when holding_hours > 336 and holding_hours <= 720 then '14 - 30 Days' when holding_hours > 720 then '> 1 Month' when holding_hours = '-1' then 'Still Holding' when holding_hours = '-2' then 'Staking' end as type, count (distinct wallet_address) as Users from maintable group by 1 order by 2 desc /*staket as ( select origin_from_address, sum (decoded_log:value/1e18) as Staked_Amount from avalanche.core.ez_decoded_event_logs t1 join claimerst t2 on t1.origin_from_address = t2.claimer and t1.decoded_log:value/1e18 <= t2.claimed_amount and t1.block_timestamp > t2.claim_date where origin_to_address = '0xeffb809d99142ce3b51c1796c096f5b01b4aaec4' and event_name = 'Transfer' and origin_function_signature = '0xb6b55f25' and contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c' and origin_from_address = decoded_log:from group by 1), unstaket as ( select origin_from_address, sum (decoded_log:value/1e18) as UnStaked_Amount from avalanche.core.ez_decoded_event_logs t1 join claimerst t2 on t1.origin_from_address = t2.claimer and t1.decoded_log:value/1e18 <= t2.claimed_amount and t1.block_timestamp > t2.claim_date where origin_to_address = '0xeffb809d99142ce3b51c1796c096f5b01b4aaec4' and event_name = 'Transfer' and origin_function_signature = '0x2e1a7d4d' and contract_address = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c' and origin_from_address = decoded_log:to group by 1), netstaket as ( select t1.origin_from_address, sum (staked_amount - unstaked_amount) as Net_Staked_Amount from staket t1 join unstaket t2 on t1.origin_from_address = t2.origin_from_address group by 1), swapt as ( select origin_from_address, sum (amount_in) as Swapped_Amount from avalanche.defi.ez_dex_swaps t1 join claimerst t2 on t1.origin_from_address = t2.claimer and t1.amount_in <= t2.claimed_amount and t1.block_timestamp > t2.claim_date where token_in = '0xb8d7710f7d8349a506b75dd184f05777c82dad0c' group by 1), transfert as ( select from_address, sum (amount) as Transferred_Amount from avalanche.core.ez_token_transfers t1 join claimerst t2 on t1.from_address = t2.claimer and t1.amount <= t2.claimed_amount and t1.block_timestamp > t2.claim_date group by 1) select 'Hold' as state, count (distinct claimer) as Users, sum (Claimed_Amount) as Amount from claimerst where claimer not in (select distinct origin_from_address from staket) and claimer not in (select distinct origin_from_address from swapt) and claimer not in (select distinct from_address from transfert) union all select 'Staked' as state, count (distinct origin_from_address) as Users, sum (Staked_Amount) as Amount from staket union all select 'Swapped Out' as state, count (distinct origin_from_address) as Users, sum (Swapped_Amount) as Amount from swapt*/

Query	Creator	Created
Distribution of $ARENA Airdrop Recipients By Holding Time ($ARENA Airdrop 30 DEC 2024) curve uniswap kyberswap trader joe swaps	Ali3N	Dec 30, 2024
$ARENA Receivers Leaderboard ($ARENA Airdrop 30 DEC 2024) curve uniswap kyberswap trader joe swaps	Ali3N	Dec 30, 2024

Ali3N