2,358 answered market questions

WITH month_ends AS
(
    SELECT
        toStartOfMonth(date)           AS month_start,
        argMax(toFloat64(close), date) AS month_end_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date < toStartOfYear(today())
    GROUP BY month_start
),
monthly_returns AS
(
    SELECT
        month_start,
        month_end_close,
        lagInFrame(month_end_close, 1) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_close
    FROM month_ends
)
SELECT
    formatDateTime(month_start, '%b %Y')               AS label,
    round((month_end_close / prev_close - 1) * 100, 2) AS sep_return_pct
FROM monthly_returns
WHERE prev_close > 0
  AND toMonth(month_start) = 9
ORDER BY sep_return_pct ASC
LIMIT 3