binhachon93. [Loop] Post-Airdrop Behavior: Group by amount received
Updated 2021-10-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
›
⌄
--------------------CALCULATE AIRDROP CLAIM-----------------------------------------------------------------------------------------------------------
with GENESIS_AIRDROP_CLAIM as (SELECT EVENT_ATTRIBUTES:"to"::string as ADDRESS, SUM(EVENT_ATTRIBUTES:"0_amount"/1e6) AS AMOUNT from terra.msg_events
WHERE EVENT_ATTRIBUTES:"0_contract_address" = 'terra1atch4d5t25csx7ranccl48udq94k57js6yh0vk'
AND EVENT_ATTRIBUTES:"0_action" = 'claim'
GROUP BY ADDRESS),
--------------------CALCULATE BUY SELL AMOUNT-----------------------------------------------------------------------------------------------------------
LP_SWAP_TABLE AS(
SELECT --BLOCK_TIMESTAMP, TX_ID, --SWAP UST FOR OTHER PAIR
EVENT_ATTRIBUTES:"to"::string AS ADDRESS,
0 AS SELL_AMOUNT,
--EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS UST_AMOUNT,
EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS BUY_AMOUNT
FROM terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND EVENT_ATTRIBUTES:"0_action"::string = 'swap'
AND EVENT_ATTRIBUTES:"0_contract_address"::string = 'terra106a00unep7pvwvcck4wylt4fffjhgkf9a0u6eu' -- INPUT LP CONTRACT HERE
UNION ALL
SELECT --BLOCK_TIMESTAMP, TX_ID, -- SWAP OTHER PAIR TO UST
EVENT_ATTRIBUTES:"from"::string AS ADDRESS,
--EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS UST_AMOUNT,
EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS SELL_AMOUNT,
0 AS BUY_AMOUNT
FROM terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND EVENT_ATTRIBUTES:"1_action"::string = 'swap'
AND EVENT_ATTRIBUTES:"1_contract_address"::string = 'terra106a00unep7pvwvcck4wylt4fffjhgkf9a0u6eu' -- INPUT LP CONTRACT HERE
),
BUY_SELL_LOOP AS(
SELECT ADDRESS, SUM(BUY_AMOUNT) AS BUY_AMOUNT, SUM(SELL_AMOUNT) AS SELL_AMOUNT FROM LP_SWAP_TABLE
GROUP BY ADDRESS
),
--------------------CALCULATE LP PROVIDER-----------------------------------------------------------------------------------------------------------
PROVIDE_LIQUIDITY AS(
SELECT MSG_VALUE:"sender"::string as ADDRESS,
MSG_VALUE:"execute_msg":"provide_liquidity":"assets"[0]:"amount"::float/1e6 as AMOUNT_PROVIDE_LIQUIDITY FROM terra.msgs
WHERE MSG_VALUE:"execute_msg":"provide_liquidity" IS NOT NULL
Run a query to Download Data