nsa2000gho
Updated 2022-10-14
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
tx_fee_cte as (
select
block_number
, sum(tx_fee) tx_fee
from ethereum.core.fact_transactions
where 1=1
and status = 'SUCCESS'
and BLOCK_TIMESTAMP::date >= '2022-09-01'
group by 1
)
, block_reward_cte as (
select
BLOCK_TIMESTAMP::date date
, case
when a.block_number < 15537394 then '1. before the merge' -- first POS block
else '2. after the merge'
end period
, a.block_number
, case
when a.block_number < 15537394 then 2
else 0
end block_reward
, BLOCK_HEADER_JSON:base_fee_per_gas base_fee_per_gas
, gas_used
, base_fee_per_gas * gas_used / 1e18 burned_fee
, case when UNCLE_BLOCKS is null then 0 else (b.block_number + 8 - a.block_number) * block_reward / 8 end uncle_reward
, case when UNCLE_BLOCKS is null then 0 else block_reward / 32 end nephew_reward
from ethereum.core.fact_blocks a
left join (
select
block_number
, hash
from ethereum.core.fact_blocks
) b on a.parent_hash = b.hash
where 1=1
Run a query to Download Data