with swaps AS (

SELECT block_timestamp::date AS date

, 'Token Swap' AS tx_type

, SUM(CASE WHEN swap_from_mint = 'o1Mw5Y3n68o8TakZFuGKLZMGjm72qv4JeoZvGiCLEvK' THEN swap_from_amount ELSE swap_to_amount END) AS sol_volume

FROM solana.defi.fact_swaps

WHERE block_timestamp > '2023-11-01'

AND (swap_from_mint = 'o1Mw5Y3n68o8TakZFuGKLZMGjm72qv4JeoZvGiCLEvK' OR swap_to_mint = 'o1Mw5Y3n68o8TakZFuGKLZMGjm72qv4JeoZvGiCLEvK')

GROUP BY 1, 2

), prices AS (

SELECT recorded_hour::date AS date

, AVG(close) AS price

FROM solana.price.ez_token_prices_hourly

WHERE token_address = 'o1Mw5Y3n68o8TakZFuGKLZMGjm72qv4JeoZvGiCLEvK'

and recorded_hour > '2023-11-01'

GROUP BY 1

SELECT date_trunc('day', s.date) AS day

, SUM( COALESCE(s.sol_volume, 0) ) AS swap_volume_sol

, SUM( price * COALESCE(s.sol_volume, 0) ) AS swap_volume_usd

FROM swaps s

JOIN prices p

ON p.date = s.date

GROUP BY 1