SocioCryptoOptimism vs L1: popularity
Updated 2022-11-17
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 'Polygon' as label,
'L1' as sub_label,
date_trunc('day',a.block_timestamp) as date,
COUNT(DISTINCT a.tx_hash) as n_txns,
avg(a.tx_fee) as avg_tx_fee,
COUNT (DISTINCT a.from_address) as n_users,
avg(matic_value*b.eth_matic) as avg_eth_value
FROM polygon.core.fact_transactions a
LEFT JOIN (
SELECT date_trunc('day',block_timestamp) as day,
median(amount_in/amount_out) as eth_matic
FROM ethereum.core.ez_dex_swaps
WHERE symbol_in = 'WETH' and symbol_out = 'MATIC'
GROUP BY day
) b
ON date_trunc('day',a.block_timestamp) = b.day
WHERE date between '2022-11-01' and CURRENT_DATE-1
GROUP BY date
UNION
SELECT 'Ethereum' as label,
'L1' as sub_label,
date_trunc('day',block_timestamp) as date,
COUNT(DISTINCT tx_hash) as n_txns,
avg(tx_fee) as avg_tx_fee,
COUNT (DISTINCT from_address) as n_users,
avg(eth_value) as avg_eth_value
FROM ethereum.core.fact_transactions
WHERE date between '2022-11-01' and CURRENT_DATE-1
GROUP BY date
UNION
SELECT 'Arbitrum' as label,
'L2' as sub_label,
date_trunc('day',block_timestamp) as date,
COUNT(DISTINCT tx_hash) as n_txns,
avg(tx_fee) as avg_tx_fee,
COUNT (DISTINCT from_address) as n_users,
Run a query to Download Data