princefarzamTotal Anchor Deposits, Last 2 Weeks
Updated 2022-01-12
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 deposits as (
SELECT
DATE_TRUNC('day',block_timestamp) as block_date,
SUM(msg_value:coins[0]:amount::float)/POW(10,6) as amount_deposited
FROM terra.msgs
WHERE
tx_status='SUCCEEDED'
AND
msg_value:contract::string='terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'
AND
msg_value:execute_msg::string='{"deposit_stable":{}}'
GROUP BY 1
ORDER BY 1),
withdraws as (
SELECT
date_trunc('day',block_timestamp) as block_date,
SUM(msg_value:execute_msg:send:amount::float/POW(10,6)) as amount_withdrawn
FROM terra.msgs
WHERE
tx_status='SUCCEEDED'
AND
msg_value:execute_msg:send:contract::string='terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'
AND
msg_value:execute_msg:send:msg:redeem_stable IS NOT NULL
GROUP BY 1
ORDER BY 1),
cumulative as (
SELECT
deposits.block_date as block_date,
SUM(amount_deposited) OVER (ORDER BY deposits.block_date) as cum_amount_deposited,
SUM(amount_withdrawn) OVER (ORDER BY deposits.block_date) as cum_amount_withdrawn,
cum_amount_deposited-cum_amount_withdrawn as cum_net_deposits
FROM deposits
JOIN withdraws ON deposits.block_date=withdraws.block_date)
SELECT *
Run a query to Download Data