eferfanU13
Updated 2022-10-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 Eth_Daily_Price as (
select
date_trunc('Day', hour) as Date,
avg (price) as ETHPrice
from ethereum.core.fact_hourly_token_prices
where symbol = 'WETH'
group by 1
)
, Optimism as (
select
'Optimism' as blockchain,
from_address ,--date_trunc('Day', A.block_timestamp)as Date ,
count (distinct tx_hash) as TX_Cnt,
count (distinct from_address) as Users_Cnt,
sum (tx_fee) as Total_ETH,
avg (tx_fee) as Average_ETH,
min (tx_fee) as Minimum_ETH,
max (tx_fee) as Maximum_ETH,
median (tx_fee) as Median_ETH,
sum (tx_fee* ETHPrice) as Total_USD,
avg (tx_fee* ETHPrice) as Average_USD,
min (tx_fee* ETHPrice) as Minimum_USD,
max (tx_fee* ETHPrice) as Maximum_USD,
median (tx_fee* ETHPrice) as Median_USD
from optimism.core.fact_transactions A
join Eth_Daily_Price B on date_trunc('Day', A.block_timestamp) = B.Date
where status = 'SUCCESS'
group by 1 ,2
)
, Ethereum as (
select
'Ethereum' as blockchain,
from_address ,--date_trunc('Day', A.block_timestamp)as Date ,
count (distinct tx_hash) as TX_Cnt,
count (distinct from_address) as Users_Cnt,
sum (tx_fee) as Total_ETH,
Run a query to Download Data