WITH ORION_CLAIM AS ( SELECT date_trunc('hour', block_timestamp) as hour, tx_id, msg_value:contract as token, msg_value:execute_msg:send:amount/pow(10,8) as amount, msg_value:execute_msg:send:contract as airdrop_sc, -- ? terra135mjmscm37mp5tu5ghp00lp374fsjaqksmjfh8 msg_value:sender as sender-- terra1exqauxdu0acpvg4pxkr246aqq9lw2hyr0tpk50 FROM terra.msgs WHERE msg_value:execute_msg:send:contract = 'terra135mjmscm37mp5tu5ghp00lp374fsjaqksmjfh8' -- airdrop_sc AND msg_value:contract = 'terra1mddcdx0ujx89f38gu7zspk2r2ffdl5enyz2u03' --orion token AND tx_status = 'SUCCEEDED' -- WHERE tx_id = '8B0C8458866DA3DE5A63D908356B4501F814A48C61F938BDC8C4E076BAC95AC4' -- https://finder.terra.money/mainnet/tx/8B0C8458866DA3DE5A63D908356B4501F814A48C61F938BDC8C4E076BAC95AC4 ), BY_ADDRESS AS ( SELECT sender as claimers, sum(amount) as total_amount, -- claimed by a user CASE WHEN sum(amount) > 0 AND sum(amount) <= 1 THEN 'a. 0-1 ORION' WHEN sum(amount) > 1 AND sum(amount) <= 10 THEN 'b. 1-10 ORION' WHEN sum(amount) > 10 AND sum(amount) <= 50 THEN 'c. 10-50 ORION' WHEN sum(amount) > 50 AND sum(amount) <= 100 THEN 'd. 50-100 ORION' WHEN sum(amount) > 100 AND sum(amount) <= 500 THEN 'e. 100-500 ORION' WHEN sum(amount) > 500 AND sum(amount) <= 1000 THEN 'f. 500-1k ORION' WHEN sum(amount) > 1000 AND sum(amount) <= 5000 THEN 'g. 1k-5k ORION' WHEN sum(amount) > 5000 AND sum(amount) <= 10000 THEN 'h. 5k-10k ORION' WHEN sum(amount) > 10000 AND sum(amount) <= 20000 THEN 'i. 10k-20k ORION' WHEN sum(amount) > 20000 AND sum(amount) <= 50000 THEN 'j. 20k-50k ORION' WHEN sum(amount) > 50000 THEN 'k. 50k and above ORION' ELSE NULL END AS claimed_tier FROM ORION_CLAIM WHERE hour >= '2021-11-15' GROUP BY 1 ) SELECT claimed_tier, count(DISTINCT claimers) as claimer, -- claimers by categories sum(total_amount) as orion_claimed, -- total claimed by category sum(orion_claimed) over (order by claimed_tier) as total_tokens_claimed, sum(claimer) over (order by claimed_tier) as cumulative_claimers FROM BY_ADDRESS GROUP BY 1 ORDER BY 1

ORION Genesis Airdrop Claims