select token_address

, recorded_hour

, close

, lag(close, 1) over (

partition by token_address

order by recorded_hour

) as prv_price

, close / prv_price as ratio

from solana.price.ez_token_prices_hourly p

where recorded_hour >= CURRENT_DATE - 1

and is_imputed = false

), pc1 as (

select recorded_hour::date as date

, token_address

from pc0

where ratio >= 10

or ratio <= 0.1

), p0 AS (

select p.token_address AS mint

, DATE_TRUNC('hour', p.recorded_hour) AS hour

, AVG(close) AS price

, MIN(close) AS min_price

from solana.price.ez_token_prices_hourly p

left join pc1

on pc1.token_address = p.token_address

and pc1.date = p.recorded_hour::date

where recorded_hour >= CURRENT_DATE - 1

and pc1.date is null

and is_imputed = FALSE

and close < 1000000

GROUP BY 1, 2

), p1 AS (

select p.token_address AS mint

, DATE_TRUNC('day', recorded_hour) AS date

, AVG(close) AS price