SELECT

token_address,

symbol,

date_trunc('day',hour) as days,

AVG(price) as day_AVG_price_USD

FROM avalanche.core.fact_hourly_token_prices as a

WHERE hour > current_date - 31

GROUP BY token_address, symbol, days

UNION ALL

SELECT

token_address,

symbol,

date_trunc('day',hour) as days,

AVG(price) as day_AVG_price_USD

FROM ethereum.core.fact_hourly_token_prices as b

WHERE hour > current_date - 31

GROUP BY token_address, symbol, days

),

top_dex_swaps_today as (

SELECT

platform as platform_today,

pool_name as pool_today,

count(DISTINCT _log_id) as swaps_today,

SUM(CASE

WHEN amount_in_USD is not NULL and amount_out_USD is not NULL

THEN (amount_in_USD + amount_out_USD)/2

WHEN in_day_AVG_price_USD is not NULL and out_day_AVG_price_USD is not NULL

THEN (amount_in*in_day_AVG_price_USD+amount_out*out_day_AVG_price_USD)/2

WHEN in_day_AVG_price_USD is not NULL

THEN amount_in*in_day_AVG_price_USD

WHEN out_day_AVG_price_USD is not NULL

THEN amount_out*out_day_AVG_price_USD

END) as swap_volume_today_dollar,

count(DISTINCT origin_from_address) as users_today