Every weekday month-end since 2020 with fewer than 1,000 filings: count, companies touched, and the neighbouring days' average

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH daily AS (
    SELECT
        filing_date,
        uniqExact(accession_number) AS filings,
        uniqExact(cik) AS companies,
        max(_ingest_time) AS last_arrived
    FROM global_markets.stocks_sec_edgar_index
    WHERE filing_date >= toDate('2019-12-20') AND filing_date <= toDate('2026-07-06')
    GROUP BY filing_date
),
month_ends AS (
    SELECT arrayJoin(arrayMap(i -> toLastDayOfMonth(addMonths(toDate('2020-01-01'), i)), range(78))) AS me
),
sparse AS (
    SELECT
        e.me AS me,
        toUInt32(ifNull(d.filings, 0)) AS filings,
        toUInt32(ifNull(d.companies, 0)) AS companies,
        d.last_arrived AS last_arrived
    FROM month_ends AS e
    LEFT JOIN daily AS d ON d.filing_date = e.me
    WHERE toDayOfWeek(e.me) <= 5 AND toUInt32(ifNull(d.filings, 0)) < 1000
)
SELECT
    toString(s.me) AS month_end,
    any(s.filings) AS filings,
    any(s.companies) AS companies,
    toUInt32(round(avgIf(n.filings, abs(dateDiff('day', n.filing_date, s.me)) <= 5 AND n.filing_date != s.me))) AS neighbour_day_avg,
    round(100 * any(s.filings) / avgIf(n.filings, abs(dateDiff('day', n.filing_date, s.me)) <= 5 AND n.filing_date != s.me), 1) AS pct_of_normal,
    toString(toDate(any(s.last_arrived))) AS last_row_arrived
FROM sparse AS s
CROSS JOIN daily AS n
GROUP BY s.me
ORDER BY s.me