scottincryptoAnchor Borrows & Earn Deposits - debug version by transaction
Updated 2021-09-07
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 borrows_repay_tx as (
select
block_timestamp,
tx_id,
date_trunc('day', block_timestamp) as date,
msg_value:sender::string as sender,
iff(msg_value:execute_msg:borrow_stable:borrow_amount::numeric is null, 0, msg_value:execute_msg:borrow_stable:borrow_amount::numeric/1e6) as borrow_amount,
iff(left(msg_value:execute_msg::string, 5) = '{"rep', msg_value:coins[0]:amount::numeric/1e6, 0) as repay_amount,
-- iff(left(msg_value:execute_msg::string, 5) = '{"dep', msg_value:coins[0]:amount::numeric/1e6, 0) as deposit_amount,
msg_value::string as msg,
borrow_amount - repay_amount as net_borrow_change
from terra.msgs
--where tx_id in ('6A9D1A99D003C1EBA11E935A4DF386F131DFCF18A19C720AC5634939F682E1B9', '0D4324D609AF231A762DE3DEED4B02A7DA3F64828E6687D9455DD2CEDEFCC4A0')
where msg_value:contract::string = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s' --anchor Market contract
and left(msg_value:execute_msg::string, 5) in ('{"rep','{"bor') --borrow, repay & deposit transactions only
and tx_status = 'SUCCEEDED'
),
borrows_by_date as (
select
date,
sender,
sum(borrow_amount) as borrows,
sum(repay_amount) as repays,
sum(net_borrow_change) as net_borrow_change
from borrows_repay_tx
group by date, sender
),
borrows_by_date_rolling as (
select
date,
sender,
borrows,
repays,
net_borrow_change,
Run a query to Download Data