News-feed attention: articles tagged NVDA during the week, decoded

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-07-06 00:00:00')
              AND published_utc < toDateTime('2026-07-11 04:00:00')
            GROUP BY d ORDER BY n DESC, d ASC LIMIT 1
        )
    ) AS peak_day,
    (
        SELECT (JSONExtractString(publisher, 'name') AS p, count() AS n)
        FROM global_markets.stocks_news
        WHERE has(tickers, 'NVDA')
          AND published_utc >= toDateTime('2026-07-06 00:00:00')
          AND published_utc < toDateTime('2026-07-11 04:00:00')
        GROUP BY p ORDER BY n DESC, p ASC LIMIT 1
    ) AS top_pub
SELECT
    count() AS week_articles,
    uniqExact(JSONExtractString(publisher, 'name')) AS publishers,
    peak_day.1 AS peak_day_date,
    peak_day.2 AS peak_day_articles,
    countIf(toDate(toTimeZone(published_utc, 'America/New_York')) = toDate('2026-07-09')) AS thursday_articles,
    peak_day.2 - countIf(toDate(toTimeZone(published_utc, 'America/New_York')) = toDate('2026-07-09')) AS peak_minus_thursday,
    top_pub.1 AS top_publisher,
    round(100.0 * top_pub.2 / count(), 1) AS top_publisher_share_pct,
    countIf(has(tickers, 'MU')) AS mu_co_articles,
    countIf(has(tickers, 'AMD')) AS amd_co_articles,
    countIf(positionCaseInsensitive(title, 'artificial intelligence') > 0 OR position(title, 'AI') > 0) AS ai_titled_articles
FROM global_markets.stocks_news
WHERE has(tickers, 'NVDA')
  AND published_utc >= toDateTime('2026-07-06 00:00:00')
  AND published_utc < toDateTime('2026-07-11 04:00:00')