with block_info as(

select

block_number

,tx_count

,block_header_json['baseFeePerGas'] as baseFeePerGas

from ethereum.core.fact_blocks

where

block_timestamp >= current_date() - interval '{{n_days}} days'

,tx_data as(

select

txs.position

,txs.tx_fee_precise - bi.baseFeePerGas * txs.gas_used * pow(10,-18) as pf

,case

when txs.position <='{{fee_tier_1}}' then 'Tier 1'

when txs.position >'{{fee_tier_1}}' and txs.position <='{{fee_tier_2}}' then 'Tier 2'

when txs.position >'{{fee_tier_2}}' then 'Tier 3'

end as tier

from ethereum.core.fact_transactions txs

inner join block_info bi on bi.block_number = txs.block_number

where

block_timestamp >= current_date() - interval '{{n_days}} days'

,group_size as(

select

tier

,cast(

pf / cast('{{binning}}' as double) as decimal (9, 5)