binhachonFlash Bounty - Critical Anchor KPIs - Deposit stats - Upgrade
Updated 2021-12-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 temp as (
SELECT
block_timestamp,
msg_value:sender::string as address,
msg_value:execute_msg:borrow_stable:borrow_amount /POW(10,6) as borrowed_amount,
row_number() over (partition by address order by block_timestamp) as rownumber
FROM terra.msgs
WHERE msg_value:execute_msg:borrow_stable IS NOT NULL
and tx_status = 'SUCCEEDED'
),
borrows as(
select block_timestamp, temp.address as address, borrowed_amount, rownumber, sum(balance_usd) as balance from temp left join terra.daily_balances on (date_trunc('day', block_timestamp) = date and temp.address = terra.daily_balances.address)
group by block_timestamp, temp.address, borrowed_amount, rownumber
),
repays as (
SELECT
block_timestamp,
msg_value:sender::string as address,
msg_value:coins[0]:amount/ POW(10,6) as amount_repaid,
row_number() over (partition by address order by block_timestamp) as rownumber
FROM terra.msgs
WHERE msg_value:execute_msg:repay_stable IS NOT NULL
and tx_status = 'SUCCEEDED'),
temp_1 as (
SELECT
block_timestamp,
msg_value:sender::string as address,
msg_value:coins[0]:amount / pow(10,6) as deposit_amount,
row_number() over (partition by address order by block_timestamp) as rownumber
FROM terra.msgs
WHERE msg_value:execute_msg:deposit_stable IS NOT NULL
and tx_status = 'SUCCEEDED'
),
terra_balance as(
select date, address, sum(balance_usd) as balance_usd from terra.daily_balances
group by date, address
Run a query to Download Data