keshanOP User Behavior 1
Updated 2022-06-23
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
›
⌄
⌄
-- UnsiwapV3Router version 1 - 0xE592427A0AEce92De3Edee1F18E0157C05861564
-- UnsiwapV3Router version 2 - 0x68b3465833fb72A70ecDF485E0e4C7bD8665Fc45
-- Both Optimism and Ethereum deployments have this same contract address.
select date_trunc('hour', l.block_timestamp) as time, contract_address, count(distinct origin_from_address) as unique_users,
avg(gas_price) as gas_price, avg(tx_fee) as avg_fee
from optimism.core.fact_event_logs l inner join optimism.core.fact_transactions using(tx_hash)
where origin_to_address in (lower('0xE592427A0AEce92De3Edee1F18E0157C05861564'), ('0x68b3465833fb72A70ecDF485E0e4C7bD8665Fc45'))
and event_name='Swap'
group by time, contract_address
order by time
--select * from optimism.core.fact_transactions where tx_hash='0x7ad550c315701a8d51287981db2a2eadf4110aedd73955006dbc3e4d7616c857'
/*
with base as (select tx_hash
from avalanche.core.fact_event_logs
where event_name = 'Swap'
and block_timestamp >= '2022-06-20'),
base2 as (select tx_hash,
sum(event_inputs:value/1e6) as swap_size
from avalanche.core.fact_event_logs
where contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'
and event_name = 'Transfer'
and tx_hash in (select tx_hash from base)
group by 1),
base3 as (select tx_hash,
sum(event_inputs:value/1e6) as transfer_size
from avalanche.core.fact_event_logs
where contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'
and event_name = 'Transfer'
and tx_hash not in (select tx_hash from base)
and block_timestamp >= '2022-06-20'
group by 1)
Run a query to Download Data