SELECT

trunc(hour, 'hour') AS recorded_hour,

AVG(price) AS avg_price_usd,

MAX(price) AS max_price,

STDDEV_POP(price) AS price_stddev

FROM

flow.price.ez_prices_hourly

WHERE

symbol ILIKE '%flow%'

AND hour >= CURRENT_DATE - INTERVAL '1 MONTH'

GROUP BY

recorded_hour

ORDER BY

recorded_hour ASC

),

flow_analysis AS (

SELECT

recorded_hour,

avg_price_usd,

max_price,

LAG(avg_price_usd, 1) OVER (ORDER BY recorded_hour) AS previous_hour_price,

price_stddev

FROM

price_data

fa.recorded_hour,

fa.avg_price_usd AS flow_price,

(fa.max_price - fa.previous_hour_price) / fa.previous_hour_price * 100 AS flow_price_change_percentage,

fa.price_stddev AS flow_price_volatility

FROM

flow_analysis fa

ORDER BY

fa.recorded_hour DESC;