mohammadhswap vs transfer 2
Updated 2022-12-08
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
›
⌄
select 'Swap' as action,
date_trunc('day',block_timestamp) as block_day,
count (distinct tx_hash) as tx_count,
count (distinct origin_from_address) as users_count,
sum(event_inputs:value/1e6) as sum_volume,
avg(event_inputs:value/1e6) as avg_volume,
sum(tx_count) over (order by block_day) as cum_tx_count,
sum(sum_volume) over (order by block_day) as cum_volume,
sum(users_count) over (order by block_day) as cum_users_count
from avalanche.core.fact_event_logs
where tx_status = 'SUCCESS'
and contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'
and event_inputs:value/1e6 < 1e9
and tx_hash in
(
select distinct TX_HASH
from avalanche.core.fact_event_logs
where EVENT_NAME ='Swap'
and block_timestamp >= '2022-07-01'
)
and event_name = 'Transfer'
and block_timestamp >= '2022-07-01'
group by action,block_day
union all
select 'Transfer' as action,
date_trunc('day',block_timestamp) as block_day,
count (distinct tx_hash) as tx_count,
count (distinct origin_from_address) as users_count,
sum(event_inputs:value/1e6) as sum_volume,
avg(event_inputs:value/1e6) as avg_volume,
sum(tx_count) over (order by block_day) as cum_tx_count,
sum(sum_volume) over (order by block_day) as cum_volume,
sum(users_count) over (order by block_day) as cum_users_count
from avalanche.core.fact_event_logs