binhachonCopy of 106. [Easy] New Money - ANC-UST pair
Updated 2021-12-07
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 Apollo_factory_contract as(
select 'terra1leadedadm2fguezmd4e445h6fe337yzq8n2dxf' as contract_address, 'HALO Community Farming' as contract_name
union
select 'terra1g7jjjkt5uvkjeyhp8ecdz4e4hvtn83sud3tmh2' as contract_address, 'Apollo main factory contract' as contract_name
),
UST_zap_in_temp as(
SELECT block_timestamp, tx_id, EVENT_ATTRIBUTES:"lp_token"::string as lp_address,
EVENT_ATTRIBUTES: "0_contract_address"::string as factory_contract,
EVENT_ATTRIBUTES:"4_amount"::float/1e6 as lp_amount,
EVENT_ATTRIBUTES:"0_amount"::float/1e6 as ust_zap_in
FROM terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND EVENT_ATTRIBUTES:"0_action" = 'zap_into_strategy'
AND EVENT_ATTRIBUTES: "0_contract_address" in (select contract_address from Apollo_factory_contract)
and tx_status = 'SUCCEEDED'
),
Apollo_transactions as(
-------------------------------------------------LP Deposit------------------------------------------------------------------------
select block_timestamp, tx_id,
event_attributes:"0_from"::string as address, event_attributes: "1_contract_address"::string as factory_contract,
event_attributes:"0_contract_address"::string as lp_address, EVENT_ATTRIBUTES:"0_amount"::float/1e6 as lp_deposit,
0 as lp_withdraw, 0 as ust_zap_in, 0 as ust_zap_out
from terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND EVENT_ATTRIBUTES:"1_action" = 'deposit_to_strategy'
AND EVENT_ATTRIBUTES: "1_contract_address" in (select contract_address from Apollo_factory_contract)
and tx_status = 'SUCCEEDED'
--------------------------------------------------------------------------------------------------------------------------------------
union all
-----------------------------------------------LP Withdraw----------------------------------------------------------------------------
SELECT block_timestamp, tx_id,
EVENT_ATTRIBUTES:"1_to"::string as address,
EVENT_ATTRIBUTES: "0_contract_address"::string as factory_contract,
event_attributes:"lp_token"::string as lp_address,
0 as lp_deposit,
EVENT_ATTRIBUTES:"withdraw_lp_amount"::float/1e6 as lp_withdraw,
Run a query to Download Data