with transaction_by_type as(

select

tx_hash,

gas_used,

position,

input_data,

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

gas_price,

effective_gas_price,

max_priority_fee_per_gas

from ethereum.core.fact_transactions

where

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

,tx_topology_tag as(

select

case

when left(input_data, 10) = '0xa9059cbb' then 'Transfer'

when left(input_data, 10) = '0x095ea7b3' then 'Approve'

when left(input_data, 10) = '0x23b872dd' then 'TransferFrom'

when left(input_data, 10) = '0x2e7ba0ef' then 'Claim'

when left(input_data, 10) = '0x415565b0' then 'TransformERC20'

when left(input_data, 10) = '0xa22cb465' then 'SetApprovalForAll'

when left(input_data, 10) = '0x2a6a935d' then 'SetSkipNFT'

when left(input_data, 10) = '0x2e1a7d4d' then 'Withdraw'

when left(input_data, 10) = '0x' then 'ETH Transfers'

else 'Others'

end as topology,

case

when position between 0 and '{{tier1_upper}}' then 'Tier1'

when position between '{{tier1_upper}}' and '{{tier2_upper}}' then 'Tier2'