SELECT

median(active_weeks),

avg(active_weeks)

FROM (

SELECT --*,

ORIGIN_FROM_ADDRESS,

count(DISTINCT date_trunc('week', block_timestamp)) as active_weeks ,

--count(DISTINCT ORIGIN_FROM_ADDRESS) as users,

sum(

CASE

when not amount_in_usd is NULL then amount_in_usd

when not amount_out_usd is NULL then amount_out_usd

--when token_price1 is NULL then token_price2 * TOKEN_OUT

--else token_price1 * TOKEN_IN end as tt

else 0 end

) as volume_usd

FROM avalanche.core.ez_dex_swaps

LEFT outer join (

SELECT

date_trunc('day', hour) as day1,

token_address as ta1,

median(price) as token_price1

FROM avalanche.core.fact_hourly_token_prices

GROUP BY 1,2

) as a on day1 = date_trunc('day', block_timestamp)

and ta1 = TOKEN_IN

LEFT outer join (

SELECT

date_trunc('day', hour) as day2,

token_address as ta2,

median(price) as token_price2

FROM avalanche.core.fact_hourly_token_prices