lunch_pov

WITH bars AS
(
    SELECT
        toTimeZone(window_start, 'America/New_York')  AS et,
        toHour(et) * 60 + toMinute(et)                AS minute_of_day,
        toFloat64(volume)                             AS shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'AAPL'
      AND window_start >= toDateTime('2026-08-12 00:00:00', 'America/New_York')
      AND window_start <  toDateTime('2026-08-13 00:00:00', 'America/New_York')
),
buckets AS
(
    SELECT
        formatDateTime(toStartOfInterval(et, INTERVAL 30 MINUTE), '%H:%i', 'America/New_York') AS et_time,
        sum(shares)                                                                            AS bucket_shares
    FROM bars
    WHERE minute_of_day >= 570
      AND minute_of_day <  960
    GROUP BY et_time
)
SELECT
    b.et_time                                        AS et_time,
    round(b.bucket_shares / 1e6, 2)                  AS aapl_volume_millions,
    toUInt32(round(b.bucket_shares * 0.10 / 1e3))    AS pov_child_fill_k_shares,
    round(100 * b.bucket_shares / t.day_shares, 1)   AS share_of_total_pct
FROM buckets AS b
CROSS JOIN
(
    SELECT sum(bucket_shares) AS day_shares
    FROM buckets
) AS t
ORDER BY et_time