SELECT date_trunc(hour, recorded_at) as hour,

symbol,

avg(price) as price

FROM osmosis.core.dim_prices

WHERE recorded_at > '2022-01-01'

GROUP BY 1,2

), dau_list as (

SELECT

tx_from as user,

date_trunc('Week', block_timestamp) as week,

count(DISTINCT block_timestamp::date) as active_day

FROM osmosis.core.fact_transactions

WHERE block_timestamp > '2022-01-01'

GROUP BY 1,2

HAVING active_day > 3

), swaps_prices as (

SELECT

date_trunc(HOUR, a.block_timestamp) as hour,

a.*,

a.from_amount/pow(10, from_decimal) as from_amount_adjusted,

b.project_name as from_symbol

FROM osmosis.core.fact_swaps as a JOIN osmosis.core.dim_labels as b ON a.from_currency = b.address

WHERE trader IN (SELECT DISTINCT user FROM dau_list) AND block_timestamp > '2022-01-01'

SELECT

sum(a.from_amount_adjusted*b.price) as usd_volume

FROM swaps_prices as a JOIN token_prices as b ON a.hour = b.hour AND a.from_symbol = b.symbol