alleriaLOOP Addresses that transferred out LOOP tokens 50-100Loop category
Updated 2021-10-23
999
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 LOOP_claimed_address as (
SELECT
--block_timestamp,
--tx_id,
msg_value:sender::string as address_claimed,
sum(msg_value:execute_msg:claim:amount / POW(10,6)) as LOOP_amount_claimed,
CASE
WHEN LOOP_amount_claimed >0 AND LOOP_amount_claimed <= 50 THEN 'a. 0 to 50 LOOP'
WHEN LOOP_amount_claimed >50 AND LOOP_amount_claimed <= 100 THEN 'b. 50 to 100 LOOP'
WHEN LOOP_amount_claimed >100 AND LOOP_amount_claimed <= 500 THEN 'c. 100 to 500 LOOP'
--WHEN LOOP_amount_claimed >200 AND LOOP_amount_claimed <= 500 THEN 'd. 200 to 500'
WHEN LOOP_amount_claimed >500 AND LOOP_amount_claimed <= 1000 THEN 'd. 500 to 1000 LOOP'
WHEN LOOP_amount_claimed >1000 THEN 'e. 1000 LOOP and above' ELSE NULL
END as airdrop_claimed_tier
FROM terra.msgs
WHERE msg_value:contract::string = 'terra1atch4d5t25csx7ranccl48udq94k57js6yh0vk'
AND msg_value:execute_msg:claim is not NULL
AND tx_status = 'SUCCEEDED'
GROUP BY address_claimed
--ORDER BY block_timestamp ASC
),
address_trade_LOOP_to_UST as (
SELECT
msg_value:sender::string as wallet_address,
sum(msg_value:execute_msg:send:amount / POW (10,6)) as LOOP_changed_to_UST
FROM terra.msgs
WHERE msg_value:sender::string in (SELECT DISTINCT address_claimed FROM LOOP_claimed_address)
AND (msg_value:contract::string = 'terra1nef5jf6c7js9x6gkntlehgywvjlpytm7pcgkn4' and msg_value:execute_msg:send is not NULL)
AND block_timestamp > to_timestamp('2021.10.07 00:00:00', 'YYYY.MM.DD HH:MI:SS') -- Date after airdrop to be safe
AND tx_status = 'SUCCEEDED'
GROUP BY msg_value:sender::string
),
address_trade_UST_to_LOOP as (
SELECT
Run a query to Download Data