gap_by_month

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
split_events AS (
    SELECT
        ticker,
        execution_date,
        max(toFloat64(split_to))   AS to_shares,
        max(toFloat64(split_from)) AS from_shares
    FROM global_markets.stocks_splits
    WHERE execution_date >= today() - 730
      AND execution_date <  today()
      AND toFloat64(split_to) > toFloat64(split_from)
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, execution_date
),
split_stories AS (
    SELECT
        arrayJoin(tickers)    AS story_ticker,
        toDate(published_utc) AS story_date
    FROM global_markets.stocks_news
    WHERE published_utc >= today() - 1140
      AND positionCaseInsensitive(title, 'split') > 0
),
gaps AS (
    SELECT
        e.ticker                                             AS ticker,
        e.execution_date                                     AS execution_date,
        dateDiff('day', min(s.story_date), e.execution_date)  AS gap_days
    FROM split_events AS e
    INNER JOIN split_stories AS s ON s.story_ticker = e.ticker
    WHERE s.story_date <  e.execution_date
      AND s.story_date >= e.execution_date - 400
    GROUP BY e.ticker, e.execution_date
)
SELECT
    toStartOfMonth(execution_date)                           AS month,
    formatDateTime(toStartOfMonth(execution_date), '%b %Y')  AS month_label,
    toUInt32(count())                                        AS split_count,
    toUInt32(round(quantileDeterministic(0.50)(toFloat64(gap_days), cityHash64(ticker, execution_date)))) AS median_gap_days,
    toUInt32(min(gap_days))                                  AS shortest_gap_days,
    toUInt32(max(gap_days))                                  AS longest_gap_days
FROM gaps
GROUP BY month, month_label
ORDER BY month