Every quarter-opening session since 2004: SPY's open-to-close move, with July 1 ranked inside it

SELECT
    count() AS quarter_opens_measured,
    round(quantileExact(0.5)(oc_pct), 2) AS median_quarter_open_pct,
    countIf(oc_pct > 0) AS quarter_opens_green,
    countIf(oc_pct <= 0) AS quarter_opens_red,
    round(anyIf(oc_pct, first_day = toDate('2026-07-01')), 2) AS jul1_oc_pct,
    arrayCount(x -> x < anyIf(oc_pct, first_day = toDate('2026-07-01')), groupArrayIf(oc_pct, first_day != toDate('2026-07-01'))) + 1 AS jul1_rank_worst_to_best,
    round(min(oc_pct), 2) AS worst_quarter_open_pct,
    round(max(oc_pct), 2) AS best_quarter_open_pct,
    concat(monthName(min(first_day)), ' ', toString(toYear(min(first_day)))) AS first_measured
FROM (
    SELECT f.first_day AS first_day, d.oc_pct AS oc_pct
    FROM (
        SELECT toStartOfQuarter(d) AS q, min(d) AS first_day
        FROM (
            SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPY'
              AND window_start >= toDateTime('2004-01-01 00:00:00')
              AND window_start < toDateTime('2026-07-02 00:00:00')
              AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
            GROUP BY d
        )
        GROUP BY q
    ) f
    INNER JOIN (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               (argMax(toFloat64(close), (window_start, close)) / argMin(toFloat64(open), (window_start, open)) - 1) * 100 AS oc_pct
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime('2004-01-01 00:00:00')
          AND window_start < toDateTime('2026-07-02 00:00:00')
          AND (toHour(window_start) * 60 + toMinute(window_start)) BETWEEN 810 AND 1199
        GROUP BY d
    ) d ON d.d = f.first_day
)