SPY volume and one-minute range by 30-minute bucket

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.

WITH
    (
        SELECT toHour(toTimeZone(toDateTime(concat(toString(max(date)), ' 12:00:00'), 'America/New_York'), 'Europe/Berlin')) - 12
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
    ) AS german_offset_hours,
    (
        SELECT sum(closing_print)
        FROM
        (
            SELECT toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS trade_date,
                   toFloat64(maxIf(size, has(conditions, 8)))            AS closing_print
            FROM global_markets.stocks_trades
            WHERE ticker = 'SPY'
              AND sip_timestamp >= today() - 60
              AND sip_timestamp <  today() - 1
            GROUP BY trade_date
        )
    ) AS closing_prints_total
SELECT
    formatDateTime(toDateTime(bucket_min * 60, 'UTC'), '%H:%i')                                            AS et_time,
    formatDateTime(toDateTime(((bucket_min + german_offset_hours * 60) % 1440) * 60, 'UTC'), '%H:%i')       AS german_time,
    round((toFloat64(sum(volume)) + if(bucket_min = 960, closing_prints_total, 0))
          / countDistinct(session_date) / 1e6, 2)                                                           AS avg_volume_millions,
    round(avg(bar_range_pct), 3)                                                                           AS avg_range_pct
FROM
(
    SELECT
        toDate(et)                                     AS session_date,
        intDiv(toHour(et) * 60 + toMinute(et), 30) * 30 AS bucket_min,
        volume,
        toFloat64(high - low) / toFloat64(close) * 100  AS bar_range_pct
    FROM
    (
        SELECT
            toTimeZone(window_start, 'America/New_York') AS et,
            volume,
            high,
            low,
            close
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 60
          AND window_start <  today() - 1
    )
    WHERE (toHour(et) * 60 + toMinute(et)) >= 540
      AND (toHour(et) * 60 + toMinute(et)) <  1020
)
GROUP BY bucket_min
ORDER BY bucket_min