half_day_volume

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
spy AS
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York'))       AS session_date,
        toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_min
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2021-12-01'
),
early AS
(
    SELECT session_date
    FROM spy
    WHERE et_min >= 570 AND et_min < 960
    GROUP BY session_date
    HAVING count() BETWEEN 180 AND 240
       AND max(et_min) < 790
),
tape AS
(
    SELECT
        date,
        toFloat64(sum(volume)) AS shares
    FROM global_markets.stocks_daily_aggs
    WHERE date >= '2021-11-01'
    GROUP BY date
),
rolled AS
(
    SELECT
        date,
        shares,
        avg(shares) OVER (ORDER BY date ASC ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS shares_20
    FROM tape
)
SELECT
    toString(r.date)                                 AS session_date,
    concat(toString(toYear(r.date)), '年',
           toString(toMonth(r.date)), '月',
           toString(toDayOfMonth(r.date)), '日')     AS session_label,
    round(r.shares / 1e8, 1)                         AS half_day_oku,
    round(r.shares_20 / 1e8, 1)                      AS trailing20_oku,
    round(100 * r.shares / r.shares_20, 1)           AS vs_trailing20_pct
FROM rolled AS r
INNER JOIN early AS e ON e.session_date = r.date
ORDER BY r.date