CryptoLionLiquidity
Updated 2021-07-02
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 prices as(
SELECT
date_trunc('day',block_timestamp) as day,
currency,
symbol,
last_value(price_usd) over (partition by symbol order by day) as price
FROM terra.oracle_prices
WHERE block_timestamp >= CURRENT_DATE - 2
-- GROUP BY 2,3,4
)
SELECT
date_trunc('day',block_timestamp) as day,
SUM(msg_value:execute_msg:provide_liquidity:assets[0]:amount / POW(10,6)) as token_0_amount,
msg_value:execute_msg:provide_liquidity:assets[0]:info:token:contract_addr::string as token_0_address,
t0.address_name as token_0_address_name,
SUM(msg_value:execute_msg:provide_liquidity:assets[1]:amount / POW(10,6)) as token_1_amount,
msg_value:execute_msg:provide_liquidity:assets[1]:info:native_token:denom::string as token_1_address,
t1.symbol as token_1_address_name,
msg_value:contract::string as contract_address,
c.address_name as contract_label
FROM terra.msgs m
LEFT OUTER JOIN terra.labels t0
ON msg_value:execute_msg:provide_liquidity:assets[0]:info:token:contract_addr = t0.address
LEFT OUTER JOIN prices t1
ON msg_value:execute_msg:provide_liquidity:assets[1]:info:native_token:denom::string = t1.currency
LEFT OUTER JOIN terra.labels c
ON msg_value:contract = c.address
WHERE msg_value:execute_msg:provide_liquidity IS NOT NULL --Ensures we only look for adding liquidity events
AND contract_label IS NOT NULL
AND token_1_address IS NOT NULL
AND token_0_address IS NOT NULL
Run a query to Download Data