avg(close) as "Current SOL price"

from solana.core.fact_token_prices_hourly

where symbol in ('SOL')

and RECORDED_HOUR::date = (select max(RECORDED_HOUR::date) from solana.core.fact_token_prices_hourly)

),november as (select

avg(close) as "November SOL price"

from solana.core.fact_token_prices_hourly

where symbol in ('SOL')

and RECORDED_HOUR::date = '2022-11-01'

),

main as(select

avg(close) as "Average SOL price",

(avg(case when RECORDED_HOUR > CURRENT_DATE - 3 then close end) - avg(case when RECORDED_HOUR::date = '2022-11-01' then close end)) / (avg(case when RECORDED_HOUR::date = '2022-11-01' THEN close END)) * 100 as "Price Change Ratio"

from solana.core.fact_token_prices_hourly

where symbol in ('SOL')

and RECORDED_HOUR >= '2022-11-01')

select

"Current SOL price",

"November SOL price",

*

from main join current_price

join november