When headlines actually land: article counts by ET clock hour, July 2026

WITH hourly AS (
    SELECT toHour(toTimeZone(published_utc, 'America/New_York')) AS hour_num,
           count() AS articles
    FROM global_markets.stocks_news
    WHERE published_utc >= toDateTime('2026-07-01 04:00:00')
      AND published_utc < toDateTime('2026-08-01 04:00:00')
    GROUP BY hour_num
),
clock AS (
    SELECT toUInt8(arrayJoin(range(24))) AS hour_num
)
SELECT concat(leftPad(toString(clock.hour_num), 2, '0'), ':00') AS et_hour,
       ifNull(hourly.articles, 0) AS article_count,
       round(100 * ifNull(hourly.articles, 0) / sum(ifNull(hourly.articles, 0)) OVER (), 2) AS share_pct,
       round(100 * sum(ifNull(hourly.articles, 0)) OVER (ORDER BY clock.hour_num ASC)
             / sum(ifNull(hourly.articles, 0)) OVER (), 2) AS cumulative_pct
FROM clock
LEFT JOIN hourly ON clock.hour_num = hourly.hour_num
ORDER BY clock.hour_num ASC