The information flow in one row: volume, composition, and co-tags

WITH
    (
        SELECT (toString(d), n)
        FROM (
            SELECT toDate(toTimeZone(published_utc, 'America/New_York')) AS d, count() AS n
            FROM global_markets.stocks_news
            WHERE has(tickers, 'NVDA')
              AND published_utc >= toDateTime('2026-06-01 00:00:00')
              AND published_utc < toDateTime('2026-07-01 04:00:00')
            GROUP BY d ORDER BY n DESC, d ASC LIMIT 1
        )
    ) AS peak_day,
    (
        SELECT (JSONExtractString(any(publisher), 'name'), count())
        FROM global_markets.stocks_news
        WHERE has(tickers, 'NVDA')
          AND published_utc >= toDateTime('2026-06-01 00:00:00')
          AND published_utc < toDateTime('2026-07-01 04:00:00')
        GROUP BY JSONExtractString(publisher, 'name') ORDER BY count() DESC LIMIT 1
    ) AS top_pub
SELECT
    count() AS june_articles,
    uniqExact(JSONExtractString(publisher, 'name')) AS publishers,
    toString(min(toDate(toTimeZone(published_utc, 'America/New_York')))) AS first_tagged_day,
    peak_day.1 AS peak_day_date,
    peak_day.2 AS peak_day_articles,
    top_pub.1 AS top_publisher,
    top_pub.2 AS top_publisher_articles,
    round(100.0 * top_pub.2 / count(), 0) AS top_publisher_pct,
    countIf(has(tickers, 'TSLA')) AS tsla_co_articles,
    countIf(has(tickers, 'AMD')) AS amd_co_articles,
    countIf(has(tickers, 'MSFT')) AS msft_co_articles,
    countIf(has(tickers, 'AAPL')) AS aapl_co_articles
FROM global_markets.stocks_news
WHERE has(tickers, 'NVDA')
  AND published_utc >= toDateTime('2026-06-01 00:00:00')
  AND published_utc < toDateTime('2026-07-01 04:00:00')