2,272 answered market questions

WITH
    toDate('2026-09-10') AS session_day,
    [585, 600, 630, 660, 690, 720, 750, 780, 810, 840, 870, 900, 930, 960] AS checkpoints
SELECT
    formatDateTime(toDateTime(session_day, 'UTC') + checkpoints[i] * 60, '%H:%i', 'UTC') AS et_time,
    round(session_cums[i] / 1e6, 1)                                                        AS session_cumulative_millions,
    round(typical_cums[i] / 1e6, 1)                                                        AS typical_cumulative_millions,
    round(100 * typical_cums[i] / typical_cums[14], 1)                                     AS typical_share_of_session_pct
FROM
(
    SELECT
        arrayJoin(arrayEnumerate(checkpoints)) AS i,
        session_cums,
        arrayMap(k -> arrayAvg(x -> arrayElement(tupleElement(x, 2), k), arraySlice(prior, 1, 10)),
                 arrayEnumerate(checkpoints)) AS typical_cums
    FROM
    (
        SELECT
            anyIf(cums, d = session_day)                                                        AS session_cums,
            arrayReverseSort(x -> tupleElement(x, 1), groupArrayIf((d, cums), d < session_day)) AS prior
        FROM
        (
            SELECT
                d,
                arrayMap(cp -> arraySum(x -> if(tupleElement(x, 1) <= cp, tupleElement(x, 2), 0), mv),
                         checkpoints) AS cums
            FROM
            (
                SELECT d, groupArray((minute_of_day, vol)) AS mv
                FROM
                (
                    SELECT
                        toDate(toTimeZone(window_start, 'America/New_York'))      AS d,
                        toHour(toTimeZone(window_start, 'America/New_York')) * 60
                          + toMinute(toTimeZone(window_start, 'America/New_York')) AS minute_of_day,
                        max(toFloat64(volume))                                     AS vol
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE ticker = 'AAPL'
                      AND window_start >= toDateTime(session_day - 20, 'America/New_York')
                      AND window_start <  toDateTime(session_day + 1, 'America/New_York')
                    GROUP BY d, minute_of_day
                    HAVING minute_of_day >= 570 AND minute_of_day <= 960
                )
                GROUP BY d
            )
        )
        HAVING length(session_cums) = 14 AND length(prior) >= 10
    )
)
ORDER BY i