2,358 answered market questions

WITH monthly AS (
    SELECT toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
           argMax(close, window_start) AS month_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2005-04-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-04-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY month_start
),
month_returns AS (
    SELECT m1.month_start AS month_start,
           100 * (toFloat64(m1.month_close) / toFloat64(m0.month_close) - 1) AS ret_pct
    FROM monthly AS m1
    INNER JOIN monthly AS m0 ON m0.month_start = addMonths(m1.month_start, -1)
)
SELECT formatDateTime(month_start, '%b') AS label,
       count() AS readings,
       round(avg(ret_pct), 2) AS avg_return_pct,
       round(quantileDeterministic(0.5)(ret_pct, cityHash64(month_start)), 2) AS median_return_pct,
       countIf(ret_pct > 0) AS positive_readings
FROM month_returns
GROUP BY label
ORDER BY min(toMonth(month_start))