Query editor
-- 9 Months Time SeriesWITH days AS (SELECT generate_series('2022-01-01'::timestamp, DATE_TRUNC('hour', NOW()), '1 hour') AS hour), price_series AS (SELECTDATE_TRUNC('hour', minute) AS hour, symbol, AVG(price) AS priceFROM prices."usd"WHERE symbol IN ('USDC', 'USDT', 'DAI', 'BUSD', 'LUSD', 'FRAX')AND DATE_TRUNC('day', minute) > NOW() - INTERVAL '9 months'GROUP BY hour, symbolUNION ALLSELECThour, 'upper_threshold' AS symbol, 1.01 AS priceFROM daysWHERE hour > NOW() - INTERVAL '9 months'UNION ALLSELECThour, 'lower_threshold' AS symbol, 0.99 AS priceFROM daysWHERE hour > NOW() - INTERVAL '9 months'UNION ALLSELECThour, 'middle_upper_threshold' AS symbol, 1.005 AS priceFROM daysWHERE hour > NOW() - INTERVAL '9 months'UNION ALLSELECThour, 'middle_lower_threshold' AS symbol, 0.995 AS priceFROM daysWHERE hour > NOW() - INTERVAL '9 months'), deviations AS (SELECThour, symbol, price, SUM(count_upper_deviation) OVER(PARTITION BY symbol ORDER BY hour ASC) AS total_upper_deviations, SUM(count_middle_deviation) OVER(PARTITION BY symbol ORDER BY hour ASC) AS total_middle_deviations