ML63up
Updated 2023-01-22Copy 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
33
34
35
36
›
⌄
with fees as (select sum(tx_fee) as total_fee , avg(tx_fee) as avg_fee , case
when block_timestamp::date<'2023-01-12' then 'before upgrade'
when block_timestamp::date='2023-01-12' then 'upgrade'
when block_timestamp::date>'2023-01-12' then 'after upgrade' end as split,
block_timestamp::date as tdate
from optimism.core.fact_transactions where year(BLOCK_TIMESTAMP)=2023
group by tdate)
, transactions as (select count(*) as total_transactions , count(distinct from_address ) as unique_users , sum(eth_value) as amount_transations
, avg(eth_value) as avg_amount_transations,case
when block_timestamp::date<'2023-01-12' then 'before upgrade'
when block_timestamp::date='2023-01-12' then 'upgrade'
when block_timestamp::date>'2023-01-12' then 'after upgrade' end as split,
block_timestamp::date as tdate
from optimism.core.fact_transactions where year(BLOCK_TIMESTAMP)=2023
group by tdate)
, newusers_first as (select from_address , min(block_timestamp::date) as first_user_transactions from optimism.core.fact_transactions
group by from_address)
, newusers as (select count(*) as new_users, first_user_transactions ,case
when first_user_transactions<'2023-01-12' then 'before upgrade'
when first_user_transactions='2023-01-12' then 'upgrade'
when first_user_transactions>'2023-01-12' then 'after upgrade' end as split
from newusers_first
where year(first_user_transactions)=2023
group by first_user_transactions)
, eth_transfer as (select count(*) as transfers , sum(amount) as amount , avg(amount) as avg_amount , count(distinct eth_from_address) as users , case
when block_timestamp::date<'2023-01-12' then 'before upgrade'
when block_timestamp::date='2023-01-12' then 'upgrade'
when block_timestamp::date>'2023-01-12' then 'after upgrade' end as split,
block_timestamp::date as tdate
from optimism.core.ez_eth_transfers where year(BLOCK_TIMESTAMP)=2023
group by tdate)
Run a query to Download Data