princefarzamProvide bETH
Updated 2022-02-05
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
›
⌄
WITH provide_bETH as(
SELECT
BLOCK_TIMESTAMP,
EVENT_ATTRIBUTES:collaterals[0]:amount::INT/POW(10,6) AS bETH_AMOUNT
FROM terra.msg_events
WHERE EVENT_ATTRIBUTES:"0_action"::string = 'lock_collateral'
AND EVENT_ATTRIBUTES:collaterals[0]:denom::string='terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun'
and tx_status = 'SUCCEEDED'
and BLOCK_TIMESTAMP >= '2021-11-03' --90 days ago
GROUP BY 1,2
ORDER by 1 ASC
),
cum_bETH AS(
SELECT
BLOCK_TIMESTAMP,
bETH_AMOUNT,
SUM(bETH_AMOUNT) OVER (ORDER BY BLOCK_TIMESTAMP ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATE_bETH,
(SELECT
avg(price_usd)
from terra.oracle_prices
where symbol= 'mETH'
and BLOCK_TIMESTAMP >= '2021-11-03') * CUMULATE_bETH AS CUMULATE_bETH_IN_UST
FROM provide_bETH
WHERE BLOCK_TIMESTAMP >= '2021-11-03'
ORDER by 1 ASC
)
SELECT
CB.BLOCK_TIMESTAMP,
PB.bETH_AMOUNT,
CB.CUMULATE_bETH,
CB.CUMULATE_bETH_IN_UST
FROM cum_bETH CB FULL OUTER JOIN provide_bETH PB ON CB.BLOCK_TIMESTAMP=PB.BLOCK_TIMESTAMP
WHERE CB.BLOCK_TIMESTAMP >= '2021-11-03'
ORDER by 1 ASC
Run a query to Download Data