scottincryptomAssets with Airdrop Count
Updated 2021-08-20
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 mir_ust as (SELECT DISTINCT
-- msg_value:sender::string as sender,
event_attributes:owner::string as sender
-- 'provide_liquidity' as action
FROM terra.msg_events
INNER JOIN terra.labels on (terra.labels.address = terra.msg_events.event_attributes:contract_address::string)
where msg_index = 0
AND event_index = 1
AND address_name LIKE 'm%'
-- AND tx_id = '5B7DBF74CE649CF937845F991F6275186177853506824F42739310956D14B2A0'
AND event_attributes:action::string = 'increase_allowance'
),
pre_query as (
SELECT
msg_value:sender::string as sender,
l.label,
sum(msg_value:execute_msg:claim:amount)/1e6 as amount,
count(tx_id) as airdrop_count
FROM terra.msgs m
left join terra.labels l on (m.msg_value:sender::string = l.address)
WHERE
msg_value:contract::string = 'terra1kalp2knjm4cs3f59ukr4hdhuuncp648eqrgshw' -- MIR airdrop contract address
-- tx_id = '5902E5EA9324DD96735C354091A4F6EB0F4619D616496314A523464FF4E930B3' -- airdrop transaction
group by 1,2
),
ad_status as (
select
m.sender,
iff(p.airdrop_count is null, 'No Airdrop', 'Airdrop') as airdrop_status
from mir_ust m left join pre_query p on (m.sender = p.sender)
)
select
airdrop_status,
Run a query to Download Data