with token_price as (

SELECT

date_trunc('d', hour) as dates,

token_address,

median(price) as usd_price

FROM avalanche.core.fact_hourly_token_prices

WHERE dates >= DATEADD('month', -6, CURRENT_DATE)

GROUP BY 1 , 2

ORDER BY 1 DESC

SELECT

--date_trunc('d', block_timestamp) as date,

platform,

count(DISTINCT tx_hash) as n_swaps,

count(DISTINCT origin_from_address) as swappers,

coalesce(sum(amount_in*c.usd_price),0) as volume_in_usd,

coalesce(sum(amount_out*d.usd_price),0) as volume_out_usd,

volume_in_usd + volume_out_usd as total_vol_usd

FROM avalanche.core.ez_dex_swaps a

LEFT JOIN token_price c ON a.token_in = c.token_address AND a.block_timestamp::date = c.dates

LEFT JOIN token_price d ON a.token_out = d.token_address AND a.block_timestamp::date = d.dates

WHERE

block_timestamp >= DATEADD('month', -6, CURRENT_DATE)

AND block_timestamp < current_date

AND platform IS NOT NULL

GROUP BY 1

ORDER BY total_vol_usd DESC

LIMIT 20