boomer77top depositor to Earn holdings
Updated 2021-12-20
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 raw as (select msg_value:sender::string as users, count(distinct tx_id) as tx_count, sum(msg_value:coins[0]:amount/1e6) as amount, ROW_NUMBER() OVER (ORDER BY tx_count desc) as rank
from terra.msgs
where msg_module = 'wasm'
and msg_value:contract = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'
and msg_value:coins[0]:denom = 'uusd'
and msg_value:execute_msg:deposit_stable::string is not null
and tx_status = 'SUCCEEDED'
and date(block_timestamp) >= CURRENT_DATE - 30
group by 1),
top as (select users, tx_count, amount, rank
from raw
where rank < 11),
staked_luna as (select address, balance as staked_luna
from terra.daily_balances
where balance_type = 'staked' and currency = 'LUNA' and date = CURRENT_DATE - 1
and address in (select users from top)),
liquid_luna as (select address, balance as liquid_luna
from terra.daily_balances
where balance_type = 'liquid' and currency = 'LUNA' and date = CURRENT_DATE - 1
and address in (select users from top)),
ust as (select address, balance as ust
from terra.daily_balances
where currency = 'UST' and date = CURRENT_DATE - 1
and address in (select users from top)),
krt as (select address, balance as krt
from terra.daily_balances
where currency = 'KRT' and date = CURRENT_DATE - 1
and address in (select users from top))
select a.users, a.rank, a.tx_count, a.amount, b.staked_luna, c.liquid_luna, d.ust, e.krt
from top a
Run a query to Download Data