SPY by half-hour: March 9, 2020 regular session, closing cross print in the final half hour

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

SELECT
    b.et_time                                                                   AS et_time,
    b.bucket_close                                                              AS bucket_close,
    b.bucket_low                                                                AS bucket_low,
    round((b.bar_shares + if(b.et_time = '15:30', p.print_shares, 0)) / 1e6, 1) AS shares_m
FROM
(
    SELECT
        formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
        round(toFloat64(argMax(close, window_start)), 2) AS bucket_close,
        round(min(toFloat64(low)), 2) AS bucket_low,
        toFloat64(sum(volume)) AS bar_shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2020-03-09 04:00:00') AND window_start < toDateTime('2020-03-09 23:59:00')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY et_time
) AS b
CROSS JOIN
(
    SELECT toFloat64(maxIf(size, has(conditions, 8))) AS print_shares
    FROM global_markets.stocks_trades
    WHERE ticker = 'SPY'
      AND sip_timestamp >= toDateTime('2020-03-09 20:00:00', 'UTC')
      AND sip_timestamp <  toDateTime('2020-03-09 20:15:00', 'UTC')
) AS p
ORDER BY et_time