hourly_prices as (

-- hourly prices

select

token_address

, date_trunc('hour', recorded_hour) as hour

, avg(close) as hourly_price

from solana.price.ez_token_prices_hourly p

where

date(recorded_hour) between date('2023-11-11') and date('2023-12-17')

and is_imputed = FALSE

group by 1, 2

),

n as (

-- check the popularity of the mint

select

swap_to_mint as mint

, count(distinct swapper) as n_swappers

from solana.defi.fact_swaps s

where

date(block_timestamp) between date('2022-11-11') and date('2023-12-17')

and succeeded = TRUE

group by 1

date_trunc('hour',fs.block_timestamp) as timestamp

, fs.swapper

, count(distinct fs.tx_id) as swaps

-- take the price of the more popular mint (idk, maybe more likely to be accurate price)

, sum(case when coalesce(nf.n_swappers, 0) > coalesce(nt.n_swappers, 0) then hp1.hourly_price * fs.swap_from_amount else hp2.hourly_price * fs.swap_to_amount end) as swap_amount_usd

from solana.defi.fact_swaps fs

left join n nf on nf.mint = fs.swap_from_mint

left join n nt on nt.mint = fs.swap_to_mint

left join hourly_prices hp1 on hp1.hour = date_trunc('hour', fs.block_timestamp)