June 29's corporate calendar and information flow, in one row

WITH
    (
        SELECT (argMax(d, n), max(n))
        FROM (
            SELECT ex_dividend_date AS d, count() AS n
            FROM global_markets.stocks_dividends
            WHERE ex_dividend_date BETWEEN '2026-06-22' AND '2026-07-02'
            GROUP BY d
        )
    ) AS peak_ex_div,
    (
        SELECT (countIf(form_type = '424B2'), countIf(form_type = '4'), countIf(form_type = '8-K'), count())
        FROM global_markets.stocks_sec_edgar_index
        WHERE filing_date = '2026-06-29'
    ) AS filings,
    (
        SELECT (count(), uniqExact(publisher), countIf(has(tickers, 'NVDA')))
        FROM global_markets.stocks_news
        WHERE toDate(toTimeZone(published_utc, 'America/New_York')) = '2026-06-29'
    ) AS news,
    (
        SELECT max(n)
        FROM (
            SELECT t, count() AS n
            FROM (
                SELECT arrayJoin(tickers) AS t
                FROM global_markets.stocks_news
                WHERE toDate(toTimeZone(published_utc, 'America/New_York')) = '2026-06-29'
            )
            WHERE t NOT IN ('NVDA', 'SPCX')
            GROUP BY t
        )
    ) AS runner_up_articles,
    (
        SELECT (any(split_from), any(split_to), count())
        FROM global_markets.stocks_splits
        WHERE ticker = 'HON' AND execution_date = '2026-06-29'
    ) AS hon_split,
    (
        SELECT (
            round(toFloat64(argMaxIf(close, window_start, window_start < '2026-06-27 00:00:00')), 2),
            round(toFloat64(argMaxIf(close, window_start, window_start >= '2026-06-29 00:00:00')), 2)
        )
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'HON'
          AND ((window_start >= '2026-06-26 13:30:00' AND window_start < '2026-06-26 20:00:00')
            OR (window_start >= '2026-06-29 13:30:00' AND window_start < '2026-06-29 20:00:00'))
    ) AS hon_close
SELECT
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date = '2026-06-29') AS ex_dividend_records_jun29,
    (SELECT count() FROM global_markets.stocks_dividends WHERE ex_dividend_date = '2026-06-29'
        AND ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'GOOGL', 'GOOG', 'META', 'TSLA', 'JPM', 'JNJ',
                       'XOM', 'KO', 'PG', 'V', 'MA', 'HD', 'WMT', 'CVX', 'MRK', 'PEP',
                       'SPY', 'QQQ', 'DIA', 'IWM', 'VTI')) AS household_name_ex_dividends,
    length(['AAPL', 'MSFT', 'NVDA', 'AMZN', 'GOOGL', 'GOOG', 'META', 'TSLA', 'JPM', 'JNJ',
            'XOM', 'KO', 'PG', 'V', 'MA', 'HD', 'WMT', 'CVX', 'MRK', 'PEP',
            'SPY', 'QQQ', 'DIA', 'IWM', 'VTI']) AS household_names_checked,
    concat(monthName(peak_ex_div.1), ' ', toString(toDayOfMonth(peak_ex_div.1))) AS busiest_ex_div_day_of_window,
    peak_ex_div.2 AS busiest_ex_div_day_records,
    (SELECT count() FROM global_markets.stocks_splits WHERE execution_date = '2026-06-29') AS splits_executed,
    (SELECT count() FROM global_markets.stocks_ipos WHERE listing_date = '2026-06-29') AS ipos_listed_jun29,
    (SELECT arrayStringConcat(groupArray(ticker), ', ') FROM (
        SELECT ticker FROM global_markets.stocks_ipos WHERE listing_date = '2026-07-01' ORDER BY ticker
    )) AS jul1_ipo_debuts,
    filings.4 AS sec_filings,
    filings.1 AS prospectus_424b2_filings,
    filings.2 AS insider_form4_filings,
    filings.3 AS filings_8k,
    news.1 AS news_articles,
    news.2 AS news_publishers,
    news.3 AS nvda_articles,
    runner_up_articles AS next_most_covered_articles,
    news.3 - runner_up_articles AS nvda_minus_next_most_covered,
    hon_split.3 AS hon_split_records,
    hon_split.1 AS hon_split_from,
    hon_split.2 AS hon_split_to,
    hon_close.1 AS hon_close_jun26,
    hon_close.2 AS hon_close_jun29