from arbitrum.core.fact_token_transfers

where origin_to_address = lower('0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9')

and contract_address = lower('0x912ce59144191c1204e64559fe8253a0e49e6548')

and from_address = origin_to_address),

price as (select

HOUR::date as day,

SYMBOL,

decimals,

avg(price) as token_price

from ethereum.core.fact_hourly_token_prices

group by 1,2,3

),

t1 AS (

SELECT

swaps.event_index,

swaps.block_timestamp,

swaps.tx_hash AS tx,

swaps.origin_from_address AS swapper,

swaps.contract_address AS token_contract,

p.symbol AS token,

(swaps.event_inputs:value / POW(10, a.decimals) * p.token_price) AS amount

FROM arbitrum.core.fact_event_logs swaps

LEFT JOIN arbitrum.core.dim_labels labels ON swaps.origin_to_address = labels.address

left join arbitrum.core.dim_contracts a on swaps.contract_address=a.ADDRESS

JOIN price p ON swaps.block_timestamp::date = p.day AND a.SYMBOL = p.SYMBOL

WHERE

swaps.tx_hash IN (

SELECT DISTINCT tx_hash

FROM arbitrum.core.fact_event_logs

WHERE tx_status = 'SUCCESS' AND event_name = 'Swap'

)

AND swaps.event_name = 'Transfer'

and a.NAME not ilike '%lp token%' and a.SYMBOL not ilike '%/USD%' and a.SYMBOL not ilike 'LP-%'