price as (

select

hour::date as date,

token_address,

avg(price) as avg_price

from

polygon.core.fact_hourly_token_prices

group by

1,

2

),

maintb as (

select

s.*,

amount_in * p.avg_price as amt_in_usd,

amount_out * pc.avg_price as amt_out_usd

from

polygon.core.ez_dex_swaps s

join price p on token_in = p.token_address

and block_timestamp::date = p.date

join price pc on token_out = pc.token_address

and block_timestamp::date = pc.date

where

platform like 'quickswap-v3'

and amount_in_usd is not null

and amount_in_usd != 0

),

final as (

select

m.*,

DECODED_LOG:"fee" / 1e6 as fee,

fee * amount_in_usd as fee_usd

from

polygon.core.ez_decoded_event_logs

left join maintb m using (tx_hash)