CryptoLionMIR weekly rewards airdrop proportion of staking rewards
Updated 2021-08-18
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 rewards as (SELECT
date_trunc('week',m.block_timestamp) as day,
-- m.tx_id,
-- m.msg_value:sender::string as u_address,
sum(event_attributes:"1_amount"::float/POW(10,6)) as amount,
sum(amount) over (order by day) as aggregate_amount
-- event_attributes:"0_action"::string as action
FROM terra.msgs m
INNER JOIN terra.msg_events me on m.tx_id = me.tx_id
WHERE
-- m.tx_id = '1DB3395685CF54199E299AF306A06B89578D422BF1E3657002DFFD7BFE3DC4A4' --reward claim tx
msg_value:contract::string = 'terra17f7zu97865jmknk7p2glqvxzhduk78772ezac5' --MIR staking address
AND msg_value:execute_msg:withdraw = '{}' --claim
AND event_attributes:"0_action" LIKE 'withdra%'
AND event_index = 1
-- AND m.block_timestamp >= getdate() - interval '1 month'
AND event_attributes:"1_amount"::float/POW(10,6) <> 0
GROUP BY 1
ORDER BY 1 DESC)
,
airdrop as (SELECT
-- block_timestamp,
date_trunc('week', block_timestamp) as date,
-- msg_value,
sum(msg_value:execute_msg:claim:amount)/1e6 as amount,
sum(amount) over (order by date) as aggregate_amount
-- msg_value:execute_msg:claim:stage,
-- msg_value:sender::string as sender
FROM terra.msgs
WHERE
msg_value:contract::string = 'terra1kalp2knjm4cs3f59ukr4hdhuuncp648eqrgshw' -- MIR airdrop contract address
-- tx_id = '5902E5EA9324DD96735C354091A4F6EB0F4619D616496314A523464FF4E930B3' -- airdrop transaction
group by date)
SELECT
day,
rewards.amount as reward_amount,
Run a query to Download Data