SELECT

tx_hash

, block_timestamp

, ORIGIN_FROM_ADDRESS as trader

, concat('0x',RIGHT(regexp_substr_all(SUBSTR(DATA,3,len(DATA)),'.{64}')[1], 40)) as account

, CASE WHEN ORIGIN_FUNCTION_SIGNATURE='0x6945b123' THEN 'buy' ELSE 'sell' END as trade_type

, ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA,3,len(DATA)),'.{64}')[3]) as units

, TO_NUMBER(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA,3,len(DATA)),'.{64}')[4]))/1e18 as fee_deducted_eth

, TO_NUMBER(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA,3,len(DATA)),'.{64}')[5]))/1e18 as protocol_fee

, TO_NUMBER(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA,3,len(DATA)),'.{64}')[6]))/1e18 as account_fee

, (fee_deducted_eth + protocol_fee + account_fee) as trade_value

, case when units = 0 then trade_value else trade_value / units end as value_per_token

from base.core.fact_event_logs

WHERE

ORIGIN_FUNCTION_SIGNATURE IN ('0x6945b123', '0xb51d0534')

AND contract_address = '0xcf205808ed36593aa40a44f10c7f7c2f67d4a4d4'

, value_per_token

, account

, trader

, tx_hash

from base_wallet

where account = '{{account_lookup}}'

order by block_timestamp desc