with transaction_by_type_raw as(

select

tx_hash,

block_number,

gas_used,

position,

input_data,

max_priority_fee_per_gas,

tx_fee_precise

from ethereum.core.fact_transactions

where

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

,transaction_by_type as(

select

ty.*,

ty.tx_fee_precise - bi.block_header_json['baseFeePerGas'] * pow(10,-18) * ty.gas_used as pf,

(ty.tx_fee_precise - bi.block_header_json['baseFeePerGas'] * pow(10,-18) * ty.gas_used) * pow(10,9) / ty.gas_used as pf_ug

--,bi.gas_used / bi.gas_limit as gas_share

from transaction_by_type_raw ty

inner join ethereum.core.fact_blocks bi on bi.block_number = ty.block_number

where

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