3,293 answered market questions

WITH toDate('2026-09-10') AS session_day
SELECT
    tupleElement(r, 1)           AS definition,
    round(tupleElement(r, 2), 2) AS at_10_30_et_ratio,
    round(tupleElement(r, 3), 2) AS at_close_ratio
FROM
(
    SELECT
        arrayJoin([
            ('Full-day basis, 63-session (3-month) average', session_1030 / avg63_full, session_close / avg63_full, 1),
            ('Full-day basis, 50-session average',           session_1030 / avg50_full, session_close / avg50_full, 2),
            ('Full-day basis, 10-session average',           session_1030 / avg10_full, session_close / avg10_full, 3),
            ('Same-time basis, 50-session average',          session_1030 / avg50_same, session_close / avg50_full, 4),
            ('Same-time basis, 10-session average',          session_1030 / avg10_same, session_close / avg10_full, 5)
        ]) AS r
    FROM
    (
        SELECT
            anyIf(cum_1030, d = session_day)                                                                    AS session_1030,
            anyIf(cum_close, d = session_day)                                                                   AS session_close,
            arrayReverseSort(x -> tupleElement(x, 1), groupArrayIf((d, cum_1030, cum_close), d < session_day)) AS prior,
            arrayAvg(x -> tupleElement(x, 3), arraySlice(prior, 1, 63))                                        AS avg63_full,
            arrayAvg(x -> tupleElement(x, 3), arraySlice(prior, 1, 50))                                        AS avg50_full,
            arrayAvg(x -> tupleElement(x, 3), arraySlice(prior, 1, 10))                                        AS avg10_full,
            arrayAvg(x -> tupleElement(x, 2), arraySlice(prior, 1, 50))                                        AS avg50_same,
            arrayAvg(x -> tupleElement(x, 2), arraySlice(prior, 1, 10))                                        AS avg10_same
        FROM
        (
            SELECT
                d,
                sumIf(vol, minute_of_day <= 630) AS cum_1030,
                sum(vol)                          AS cum_close
            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 - 100, '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 <= 959
                UNION ALL
                SELECT
                    toDate(toTimeZone(sip_timestamp, 'America/New_York'))     AS d,
                    960                                                        AS minute_of_day,
                    toFloat64(maxIf(size, has(conditions, 8)))                 AS vol
                FROM global_markets.stocks_trades
                WHERE ticker = 'AAPL'
                  AND sip_timestamp >= toDateTime(session_day - 100, 'America/New_York')
                  AND sip_timestamp <  toDateTime(session_day + 1, 'America/New_York')
                GROUP BY d
                HAVING countIf(has(conditions, 8)) > 0
            )
            GROUP BY d
        )
        HAVING length(prior) >= 63 AND session_close > 0
    )
)
ORDER BY tupleElement(r, 4)