The seasonal gap by era: average half-year returns and the spread between them

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
),
pairs AS (
    SELECT toYear(apr_m.month_start) AS season_year,
           toFloat64(oct_m.month_close) / toFloat64(apr_m.month_close) - 1 AS may_oct,
           toFloat64(apr_next.month_close) / toFloat64(oct_m.month_close) - 1 AS nov_apr
    FROM monthly AS apr_m
    INNER JOIN monthly AS oct_m ON oct_m.month_start = addMonths(apr_m.month_start, 6)
    INNER JOIN monthly AS apr_next ON apr_next.month_start = addMonths(apr_m.month_start, 12)
    WHERE toMonth(apr_m.month_start) = 4
),
tagged AS (
    SELECT may_oct,
           nov_apr,
           arrayJoin(['All seasons 2005-2025',
                      if(season_year <= 2014, 'First half 2005-2014', 'Second half 2015-2025')]) AS era
    FROM pairs
)
SELECT era,
       count() AS season_count,
       round(100 * avg(may_oct), 2) AS avg_may_oct_pct,
       round(100 * avg(nov_apr), 2) AS avg_nov_apr_pct,
       round(100 * (avg(nov_apr) - avg(may_oct)), 2) AS winter_minus_summer_pct,
       countIf(may_oct > 0) AS summers_positive
FROM tagged
GROUP BY era
ORDER BY era