hour::date as date,

avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where symbol in ('WETH')

group by 1

)

, gmx_tx as (

select

a.block_timestamp::date as date,

'Swap' as action_type,

a.ORIGIN_FROM_ADDRESS,

a.tx_hash,

(TX_FEE * b.avg_price) as fee_usd

from arbitrum.core.fact_event_logs a join price b on a.block_timestamp::date = b.date

left JOIN arbitrum.core.fact_transactions e on a.tx_hash = e.tx_hash

where contract_address = lower ('0xaBBc5F99639c9B6bCb58544ddf04EFA6802F4064')

and event_name = 'Swap'

and tx_status= 'SUCCESS'

UNION

select

a.block_timestamp::date as date,

case

when a.origin_function_signature in ('0xe70dd2fc') then 'Short Position'

when a.origin_function_signature in ('0x332e0382','0xc9874170') then 'Long Position'

end as action_type,

a.origin_from_address,

a.tx_hash,

(tx_fee * avg_price) as fee_usd

from arbitrum.core.fact_event_logs a join arbitrum.core.fact_transactions b on a.tx_hash = b.tx_hash

join price c on a.block_timestamp::date = c.date

where a.origin_to_address = lower('0x3D6bA331e3D9702C5e8A8d254e5d8a285F223aba')

AND a.origin_function_signature in ('0x332e0382','0xc9874170', '0xe70dd2fc')