mar1na-catscatscodeCopy of optimism vs l1: uniswap v3 swaps and fees
Updated 2022-06-25
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 optimism_univ3_swaps as (
select
block_timestamp::date as block_date,
tx_hash,
from_address,
eth_value,
tx_fee
from
optimism.core.fact_transactions
where tx_hash in (select tx_hash from optimism.core.fact_event_logs where event_name = 'Swap'
and origin_to_address in (lower('0xE592427A0AEce92De3Edee1F18E0157C05861564'), lower('0x68b3465833fb72A70ecDF485E0e4C7bD8665Fc45'))) -- uniswap v3 router & uniswap v3 router 2 on optimism
-- group by block_date
limit 10
),
l1_univ3_swaps as (
select
block_timestamp::date as block_date,
count(distinct tx_hash) as n_swaps_l1,
count(distinct from_address) as n_users_l1,
n_swaps_l1/n_users_l1 as avg_swaps_per_user_l1,
sum(tx_fee) as sum_l1_fees_eth,
(sum_l1_fees_eth/n_swaps_l1)*1000000000 as avg_l1_swap_fee_gwei
from
ethereum.core.fact_transactions
where tx_hash in
(select tx_hash from ethereum.core.fact_event_logs
where origin_to_address in (lower('0xE592427A0AEce92De3Edee1F18E0157C05861564'), lower('0x7a250d5630B4cF539739dF2C5dAcb4c659F2488D'))) -- uniswap v3 router & uniswap v3 router 2 on l1 ethereum
and block_date >= '2022-06-15'
group by block_date
)
select
t1.block_date,
t1.n_swaps_optimism,
Run a query to Download Data