select block_timestamp , tx_hash , origin_from_address , contract_address , event_inputs:value::float as raw_amount

from optimism.core.fact_event_logs

where origin_to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and contract_address in ('0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9', '0x94b008aa00579c1307b0ef2c499ad98a8ce58e58','0x7f5c764cbc14f9669b88837ca1490cca17c31607', '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1')

and event_name = 'Transfer'

and event_inputs:to = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and event_inputs:from != '0x0000000000000000000000000000000000000000'

and event_inputs:from != '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and event_inputs:to != '0x0000000000000000000000000000000000000000'

and event_inputs:to != origin_from_address

and event_inputs:from = origin_from_address

and block_timestamp::date >= current_date - 14

and block_timestamp::date <= current_date - 1

and tx_status = 'SUCCESS'

),

decimal_tb as (

select block_timestamp , tx_hash , origin_from_address , contract_address , raw_amount , decimals

from base_tb

join optimism.core.dim_contracts

on address = contract_address

),

amount_tb as (

select block_timestamp , tx_hash , origin_from_address , raw_amount / pow(10, decimals) as amount

from decimal_tb

select

count(distinct tx_hash) as number_of_transaction , count(distinct origin_from_address) as number_of_user , sum(amount) as volume_usd

from amount_tb