with usert as ( select distinct TO_ADDRESS as address from ethereum.core.ez_token_transfers where BLOCK_TIMESTAMP >= '2023-09-15' and BLOCK_NUMBER >= '18142787' and contract_address = lower('0x0176b898e92e814c06cc379e508ceb571f70bd40') -- TIP and TO_ADDRESS not in ( '0x1e906a88f8f7fef31af96e1ea53718970fe36e96' -- TipClaims , '0xeb1b2622e07bcf5362b6f735e182e54e9bc75ba8' -- Uniswap V2: TIP 40 , '0x34c6f1ea29156ddcaed5f9df9035195a5f279738' -- Deployer )), balancet as ( select usert.address, zeroifnull(sum(case when FROM_ADDRESS = '0x1e906a88f8f7fef31af96e1ea53718970fe36e96' and BLOCK_TIMESTAMP <= '2023-09-21' and block_number <= '18178314' and ORIGIN_FUNCTION_SIGNATURE = '0xb69d1a08' then amount end)) as claim_amt, sum(case when usert.address = TO_ADDRESS then amount else amount * (-1) end ) as bal from usert join ethereum.core.ez_token_transfers on (usert.address = FROM_ADDRESS or usert.address = TO_ADDRESS) where BLOCK_TIMESTAMP >= '2023-09-15' and BLOCK_NUMBER >= '18142787' and contract_address = lower('0x0176b898e92e814c06cc379e508ceb571f70bd40') -- TIP group by usert.address ) SELECT case when change_percent > 0 then 'Buyers' when change_percent < 0 then 'Sellers' when change_percent = 0 then 'Holders' end as type, case when abs(change_percent) > 0 and abs(change_percent) <= 10 then 'Less than %10' when abs(change_percent) > 10 and abs(change_percent) <= 20 then 'Between %10 and %20' when abs(change_percent) > 20 and abs(change_percent) <= 50 then 'Between %20 and %50' when abs(change_percent) > 50 and abs(change_percent) <= 80 then 'Between %50 and %80' when abs(change_percent) > 80 and abs(change_percent) < 100 then 'Between %80 and %100' when abs(change_percent) = 100 then '%100' when abs(change_percent) > 100 then 'More than %100' else 'holder' end as type2, count(distinct address) as users from( select balancet.address as address, ENS_NAME, claim_amt as claimed_amount, round(case when bal < 1 then '0' else bal end,2) as current_balance, round(case when bal - claim_amt < 1 then '0' else bal - claim_amt end,2) as amount_change, round((bal - claim_amt) * 100 / claim_amt,2) as change_percent, rank() over(order by change_percent desc) as rank from balancet left join crosschain.ens.ez_ens_domains d on balancet.address = d.OWNER where claim_amt != 0 order by change_percent desc) group by 1,2

Tipcoin Airdrop ($tip)