princefarzamtotal collateral from addresses that have borrowed and deposited into anchor
Updated 2022-01-31
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 providebeth as (
SELECT
msg_value:sender::string as address,
SUM(msg_value:execute_msg:send:amount /POW(10,6)) as provide_beth
FROM terra.msgs
WHERE msg_value:execute_msg:send:msg:deposit_collateral IS NOT NULL
and msg_value:contract::string = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun' -- deposit beth
and tx_status = 'SUCCEEDED'
and block_timestamp <= '2021-08-31'
group by 1),
withdrawbeth as (
SELECT
msg_value:sender::string as address,
SUM(msg_value:execute_msg:withdraw_collateral:amount /POW(10,6)) as withdraw_beth --withdraw_collateral
FROM terra.msgs
WHERE msg_value:execute_msg:withdraw_collateral IS NOT NULL
and tx_status = 'SUCCEEDED'
and msg_value:contract::string = 'terra10cxuzggyvvv44magvrh3thpdnk9cmlgk93gmx2'
and block_timestamp <= '2021-08-31'
group by 1),
netbeth as (
select
pb.address as addresses,
sum(pb.provide_beth - (case when wb.withdraw_beth is null then 0 else wb.withdraw_beth end)) as net_beth,
( SELECT
avg(price_usd)
from terra.oracle_prices
where symbol = 'mETH'
and block_timestamp >= '2021-08-31'
and block_timestamp < '2021-09-01')*(net_beth) as beth_value
from providebeth pb
left outer join withdrawbeth wb
on pb.address = wb.address
Run a query to Download Data