CryptoLionWhat are Airdrop Recipients doing with their MIR? -Percentages
    Updated 2021-08-21
    with stage_table as (
    select
    stage, --sequence of integers from 1-54 - 1st stage is genesis airdrop, 2-54 are weekly staking reward airdrops
    720000+stage*100000 as block_height, -- airdrops run every 100k blocks starting at block 820000 - 100k blocks is approx 1 week
    b.block_timestamp,
    date_trunc('day', b.block_timestamp) as date,
    iff(stage = 1, 9150000, 345283) as max_airdrop, -- genesis drop is 9.15m MIR, weekly staking drops are 18.3m/53weeks
    iff(stage = 1, 'genesis', 'weekly') as airdrop_type
    from (select row_number() over (order by (select null)) stage from terra.labels) -- integer sequence generator (can use any table longer than the sequence required)
    left join terra.blocks b on (720000+stage*100000 = b.block_id) -- grab the block timestamps for the generated block sequence
    where stage <= 54
    order by stage
    ),

    airdrops as (
    select
    count(msg_value:execute_msg:claim:stage) as claims,
    sum(msg_value:execute_msg:claim:amount/POW(10,6)) as amount,
    msg_value:sender::string as sender
    from terra.msgs
    where msg_value:contract::string = 'terra1kalp2knjm4cs3f59ukr4hdhuuncp648eqrgshw' -- MIR airdrop contract address
    and msg_value:execute_msg:claim:amount is not null --removes the contract calls to initialise each airdrop stage
    and tx_status = 'SUCCEEDED' --would you believe that people try to claim the same airdrop twice?
    group by 3
    having claims = 35 -- this was the max claims at the time of query ~ 40 addresses had claimed this many airdrops
    order by 1 desc
    ),

    actions as (
    select
    block_timestamp as time,
    block_id,
    tx_id,
    sender,
    msg_value:execute_msg:send:msg::string,
    msg_value,
    Run a query to Download Data