MLDZMNAST5
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
›
⌄
with tb1 as (select *
from avalanche.core.fact_event_logs
where tx_status = 'SUCCESS'
and event_name ='Swap'
and block_timestamp >= '2022-07-01')
select
date_trunc('{{Time_basis}}',BLOCK_TIMESTAMP) as date,
'Swap' as actions,
count (distinct tx_hash) as no_txn,
count (distinct origin_from_address) as no_users,
sum (RAW_AMOUNT/1e6) as total_volume,
avg (RAW_AMOUNT/1e6) as average_volume,
median (RAW_AMOUNT/1e6) as Median_Volume,
avg(average_volume) OVER ( ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days,
sum(no_txn) over ( order by date) as cum_txn,
sum(no_users) over ( order by date) as cum_users,
sum(total_volume) over (order by date) as cum_volume
from avalanche.core.fact_token_transfers
where tx_hash in (select tx_hash from tb1)
and contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'
and block_timestamp >= '2022-07-01'
group by 1
union all
select
date_trunc('{{Time_basis}}',BLOCK_TIMESTAMP) as date,
'Transfer' as actions,
count (distinct tx_hash) as no_txn,
count (distinct origin_from_address) as no_users,
sum (RAW_AMOUNT/1e6) as total_volume,
avg (RAW_AMOUNT/1e6) as average_volume,
median (RAW_AMOUNT/1e6) as Median_Volume,
avg(average_volume) OVER ( ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days,
sum(no_txn) over ( order by date) as cum_txn,
Run a query to Download Data