MLDZMNbrw8
Updated 2023-03-01
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 tb1 as (select
BLOCK_TIMESTAMP,
tx_hash,
BORROWER_ADDRESS,
BORROWED_USD
from ethereum.aave.ez_borrows
),
tb2 as (
select
x.BLOCK_TIMESTAMP,
x.FROM_ADDRESS,
x.TO_ADDRESS,
x.tx_hash,
x.AMOUNT_USD,
ROW_NUMBER() OVER (partition by FROM_ADDRESS order by x.BLOCK_TIMESTAMP ) as t_n
from ethereum.core.ez_token_transfers x
join tb1 y on x.FROM_ADDRESS = y.BORROWER_ADDRESS and x.block_timestamp>y.block_timestamp
order by 1
)
select
LABEL,
count(distinct FROM_ADDRESS) as users,
count(distinct tx_hash) as count_txn,
sum(AMOUNT_USD) as volume
-- sum(users) over (partition by LABEL_TYPE order by day) as cum_users,
-- sum(volume) over (partition by LABEL_TYPE order by date) as cum_txn
from tb2 q join ethereum.core.dim_labels w on q.to_ADDRESS= w.address
where LABEL_TYPE not in ('chadmin','operator','token')
and LABEL not ilike '%aave%'
and t_n=1
and BLOCK_TIMESTAMP>=CURRENT_DATE- {{Time_period_days}}
group by 1 having volume is not null
order by 4 desc limit 10
Run a query to Download Data