3,293 answered market questions

WITH
    bars AS (
        SELECT
            ticker,
            toDate(et_ts)                                                        AS session_date,
            formatDateTime(toStartOfInterval(et_ts, INTERVAL 30 MINUTE), '%H:%i') AS et_time,
            toHour(et_ts) * 60 + toMinute(et_ts)                                 AS et_min,
            toFloat64(volume)                                                    AS vol
        FROM
        (
            SELECT
                ticker,
                toTimeZone(window_start, 'America/New_York') AS et_ts,
                volume
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker IN ('SPY', 'KO')
              AND window_start >= toDateTime(today() - 40)
        )
    ),
    slots AS (
        SELECT
            ticker,
            session_date,
            et_time,
            min(et_min) AS slot_min,
            sum(vol)    AS slot_vol
        FROM bars
        WHERE et_min >= 570 AND et_min < 960
        GROUP BY ticker, session_date, et_time
    ),
    complete AS (
        SELECT ticker, session_date
        FROM slots
        GROUP BY ticker, session_date
        HAVING max(slot_min) >= 930
    ),
    slot_avg AS (
        SELECT
            ticker,
            et_time,
            min(slot_min) AS slot_min,
            avg(slot_vol) AS avg_slot_vol
        FROM slots
        WHERE (ticker, session_date) IN (SELECT ticker, session_date FROM complete)
        GROUP BY ticker, et_time
    ),
    cum AS (
        SELECT
            ticker,
            et_time,
            slot_min,
            sum(avg_slot_vol) OVER (PARTITION BY ticker ORDER BY slot_min) AS cum_vol,
            sum(avg_slot_vol) OVER (PARTITION BY ticker)                   AS day_vol
        FROM slot_avg
    )
SELECT
    et_time,
    round(100 * maxIf(cum_vol / day_vol, ticker = 'SPY'), 1) AS spy_share_pct,
    round(100 * maxIf(cum_vol / day_vol, ticker = 'KO'), 1)  AS ko_share_pct
FROM cum
GROUP BY et_time
ORDER BY min(slot_min)