SPY: opening and closing window share of session volume, by month

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
    formatDateTime(b.month_start, '%Y-%m')                                             AS month,
    round(100 * (b.close_bars + p.print_shares) / (b.session_bars + p.print_shares), 2) AS close_window_pct,
    round(100 * b.open_bars / (b.session_bars + p.print_shares), 2)                     AS open_window_pct
FROM
(
    SELECT
        month_start,
        toFloat64(sumIf(volume, et_minute_of_day >= 955)) AS close_bars,
        toFloat64(sumIf(volume, et_minute_of_day <  575)) AS open_bars,
        toFloat64(sum(volume))                            AS session_bars
    FROM
    (
        SELECT
            toStartOfMonth(toTimeZone(window_start, 'America/New_York'))     AS month_start,
            toHour(toTimeZone(window_start, 'America/New_York')) * 60
                + toMinute(toTimeZone(window_start, 'America/New_York'))     AS et_minute_of_day,
            volume
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toStartOfMonth(today() - 730)
          AND window_start <  today() - 2
    )
    WHERE et_minute_of_day >= 570 AND et_minute_of_day <= 959
    GROUP BY month_start
) AS b
INNER JOIN
(
    SELECT toStartOfMonth(session_date) AS month_start, sum(closing_print) AS print_shares
    FROM
    (
        SELECT
            toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS session_date,
            toFloat64(maxIf(size, has(conditions, 8)))            AS closing_print
        FROM global_markets.stocks_trades
        WHERE ticker = 'SPY'
          AND sip_timestamp >= toStartOfMonth(today() - 730)
          AND sip_timestamp <  today() - 2
          AND toHour(sip_timestamp, 'America/New_York') IN (13, 16)
          AND toMinute(sip_timestamp, 'America/New_York') < 10
        GROUP BY session_date
    )
    GROUP BY month_start
) AS p ON p.month_start = b.month_start
ORDER BY b.month_start