0-MIDtotal stats 3m
Updated 2025-03-20Copy Reference Fork
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 tab1 as (
select BLOCK_TIMESTAMP::date as day
,TX_HASH
,FROM_ADDRESS
,AMOUNT
,SYMBOL
from ronin.core.ez_token_transfers
where ORIGIN_TO_ADDRESS='0x7d0556d55ca1a92708681e2e231733ebd922597d'
and ORIGIN_FROM_ADDRESS=FROM_ADDRESS
and BLOCK_TIMESTAMP::date>=current_date-90
),
tab2 as (
select HOUR::date as day
,SYMBOL
,avg(PRICE) as price_usd
from ronin.price.ez_prices_hourly
where HOUR::date>=current_date-90
group by 1,2
)
select --tab1.day
sum(AMOUNT*price_usd) as volume
,median(AMOUNT*price_usd) as med
,avg(AMOUNT*price_usd) as avg
,count(distinct TX_HASH) as swaps
,count(distinct FROM_ADDRESS) as swappers
from tab1
left join tab2
on tab1.SYMBOL=tab2.SYMBOL
and tab1.day=tab2.day
--and tab2.day is not null
QueryRunArchived: QueryRun has been archived