boomer77borrower holding
Updated 2021-12-30
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 borrow as (select tx_id, msg_value:sender::string as users, block_timestamp, ROW_NUMBER() OVER (partition by users Order by block_timestamp asc) as rank
from terra.msgs
where msg_value:execute_msg::string like '%deposit_stable%' and tx_status = 'SUCCEEDED'
),
borrowers as (select users --take this
from borrow
where rank = 1 and date(block_timestamp) >= CURRENT_DATE - 90
group by 1),
count_tx as (select users, count(distinct tx_id) as tx_count
from borrow
where users in (select users from borrowers)
group by 1),
total_borrow as (select sender, sum(amount_usd) as borrow_amount --total borrows
from anchor.borrows
where sender in (select users from borrowers)
group by 1),
p_collateral as (select sender, sum(amount) as collateral_amount --total borrows
from anchor.collateral
where sender in (select users from borrowers)
group by 1),
deposit as (select sender, sum(deposit_amount_usd) as deposit_ust --total borrows
from anchor.deposits
where sender in (select users from borrowers)
group by 1)
select a.users, a.tx_count, b.borrow_amount, c.collateral_amount, d.deposit_ust
from count_tx a
left outer join total_borrow b on a.users = b.sender
left outer join p_collateral c on a.users = c.sender
left outer join deposit d on a.users = d.sender
order by a.tx_count desc
Run a query to Download Data