RayyykAvalanche Swaps vs Transfers 3
Updated 2022-12-08Copy 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
›
⌄
with table_1 as (select min(block_timestamp) as first_tx,
'Swap' as action,
origin_from_address
from avalanche.core.fact_event_logs
where contract_address = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E')
and event_inputs:value/1e6 < 999999999
and block_timestamp >= '2022-07-01'
and tx_status = 'SUCCESS'
and tx_hash in (select distinct tx_hash from avalanche.core.fact_event_logs where event_name = 'Swap')
group by 3
union
select min(block_timestamp) as first_tx,
'Transfer' as action,
origin_from_address
from avalanche.core.fact_event_logs
where contract_address = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E')
and event_inputs:value/1e6 < 999999999
and block_timestamp >= '2022-07-01'
and tx_status = 'SUCCESS'
and not tx_hash in (select distinct tx_hash from avalanche.core.fact_event_logs where event_name = 'Swap')
group by 3),
final1 as (select date_trunc('hour', first_tx) as day,
action,
count(distinct(origin_from_address)) as new_wallets,
sum(new_wallets) over (partition by action order by day) as cumu_new_wallets
from table_1
group by 1,2)
select *
from final1
order by 1 desc
Run a query to Download Data