0-MIDavg stats per day base
Updated 2024-10-02
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 act1 as (
with tab1 as (
select BLOCK_TIMESTAMP::date as day
,TX_HASH
,ORIGIN_FROM_ADDRESS
from base.core.ez_decoded_event_logs
where ORIGIN_TO_ADDRESS='0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae'
and EVENT_NAME='LiFiGenericSwapCompleted'
and DECODED_LOG:integrator='jumper.exchange'
and BLOCK_TIMESTAMP::date>=current_date-30
and TX_STATUS='SUCCESS'
),
tab2 as (
select BLOCK_TIMESTAMP::date as day
,AMOUNT_USD
,TX_HASH
,ORIGIN_FROM_ADDRESS
,TO_ADDRESS
from base.core.ez_token_transfers
where ORIGIN_TO_ADDRESS='0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae'
and ORIGIN_FROM_ADDRESS=TO_ADDRESS
and BLOCK_TIMESTAMP::date>=current_date-30
)
select tab1.day
,sum(AMOUNT_USD) as volume
,avg(AMOUNT_USD) as avg
,count(distinct tab1.ORIGIN_FROM_ADDRESS) as swappers
,count(distinct tab1.TX_HASH) as swaps
from tab1
left join tab2
on tab1.TX_HASH=tab2.TX_HASH
and tab1.ORIGIN_FROM_ADDRESS=tab2.ORIGIN_FROM_ADDRESS
--and volume is not null
group by 1
QueryRunArchived: QueryRun has been archived