prices as (

select

hour::date as date,

symbol,

avg(price) as price_usd

from ethereum.price.ez_prices_hourly

where symbol in ('WMATIC', 'WETH', 'WBNB')

and date >= '2024-01-01'

group by 1, 2

),

main as (

select

tx_hash,

block_timestamp,

nvl(amount_in_usd, amount_out_usd) as amount_usd,

'L2' as type,

origin_from_address as user,

tx_fee,

gas_used,

tx_fee * price_usd as tx_fee_usd

from polygon.defi.ez_dex_swaps

join polygon.core.fact_transactions

using (tx_hash, block_timestamp)

left join prices on block_timestamp::date = date and symbol = 'WMATIC'

where status = 'SUCCESS'

and platform ilike 'uniswap%'

and block_timestamp >= '2024-01-01'

union all

select

tx_hash,

block_timestamp,