fastest_slowest

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,
        e.to_shares                                          AS to_shares,
        e.from_shares                                        AS from_shares,
        min(s.story_date)                                    AS first_story_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, e.to_shares, e.from_shares
)
SELECT
    ticker,
    concat(toString(to_shares), ':', toString(from_shares)) AS ratio,
    formatDateTime(first_story_date, '%b %e, %Y')           AS announced_on,
    formatDateTime(execution_date, '%b %e, %Y')             AS effective_on,
    toUInt32(gap_days)                                      AS gap_days
FROM
(
    SELECT
        *,
        row_number() OVER (ORDER BY gap_days ASC,  ticker ASC) AS quickest_rank,
        row_number() OVER (ORDER BY gap_days DESC, ticker ASC) AS longest_rank
    FROM gaps
)
WHERE quickest_rank <= 5 OR longest_rank <= 5
ORDER BY gap_days ASC, ticker ASC