sweep_day_peak

SELECT
    tupleElement(entry, 1) AS label,
    tupleElement(entry, 2) AS volume_millions
FROM
(
    SELECT
        arrayJoin([
            ('Fifteen minutes that printed the session high', peak_slice),
            ('Median fifteen-minute slice of the session',    median_slice),
            ('Busiest fifteen-minute slice of the session',   busiest_slice)
        ]) AS entry
    FROM
    (
        SELECT
            round(argMax(volume_mm, (slice_high, slice_start)), 2) AS peak_slice,
            round(quantileExact(0.5)(volume_mm), 2)                AS median_slice,
            round(max(volume_mm), 2)                               AS busiest_slice
        FROM
        (
            SELECT
                toStartOfFifteenMinutes(toTimeZone(window_start, 'America/New_York')) AS slice_start,
                toFloat64(max(high))                                                  AS slice_high,
                toFloat64(sum(volume)) / 1e6                                          AS volume_mm
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPY'
              AND window_start >= '2025-01-01'
              AND window_start <  '2026-01-02'
              AND toDate(toTimeZone(window_start, 'America/New_York')) =
              (
                  SELECT toDate(date)
                  FROM
                  (
                      SELECT
                          date,
                          hi,
                          cl,
                          lagInFrame(hi) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_hi
                      FROM
                      (
                          SELECT
                              date,
                              argMax(high,  _ingest_time) AS hi,
                              argMax(close, _ingest_time) AS cl
                          FROM global_markets.stocks_daily_aggs
                          WHERE ticker = 'SPY'
                            AND date >= '2024-12-31'
                            AND date <  '2026-01-01'
                          GROUP BY date
                      )
                  )
                  WHERE date >= '2025-01-01'
                    AND prior_hi > 0
                    AND hi > prior_hi
                    AND cl < prior_hi
                  ORDER BY (toFloat64(hi) - toFloat64(prior_hi)) / toFloat64(prior_hi) DESC, date DESC
                  LIMIT 1
              )
              AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
                   + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
              AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
                   + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
            GROUP BY slice_start
        )
    )
)