CryptoLionWhat are Airdrop Recipients doing with their MIR? -Percentages
Updated 2021-08-21
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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