ex_date_gaps

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 liquid AS
(
    SELECT ticker
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 120
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING avg(volume) >= 1000000
       AND avg(close)  >= 5
),
ex_dates AS
(
    SELECT
        d.ticker AS ticker,
        arraySort(groupUniqArray(toUInt32(toDate(d.ex_dividend_date)))) AS day_numbers
    FROM global_markets.stocks_dividends AS d
    INNER JOIN liquid AS l ON l.ticker = d.ticker
    WHERE d.ex_dividend_date >= today() - 400
      AND d.ex_dividend_date <  today()
      AND d.cash_amount > 0
    GROUP BY d.ticker
),
gaps AS
(
    SELECT arrayJoin(arrayDifference(day_numbers)) AS gap_days
    FROM ex_dates
    WHERE length(day_numbers) >= 2
)
SELECT
    bucket,
    interval_count,
    share_pct,
    median_gap_days,
    concat(toString(median_gap_days), ' ',
           multiIf((median_gap_days % 10 = 1) AND (median_gap_days % 100 != 11), 'день',
                   ((median_gap_days % 10) IN (2, 3, 4)) AND ((median_gap_days % 100 < 12) OR (median_gap_days % 100 > 14)), 'дня',
                   'дней')) AS median_gap_label
FROM
(
    SELECT
        multiIf(g.gap_days <= 45,  'шаг около месяца (до 45 дней)',
                g.gap_days <= 120, 'квартальный шаг (46-120 дней)',
                g.gap_days <= 250, 'полугодовой шаг (121-250 дней)',
                                   'годовой шаг (свыше 250 дней)') AS bucket,
        count()                                     AS interval_count,
        round(100.0 * count() / any(t.total), 1)    AS share_pct,
        toUInt16(quantileExact(0.5)(g.gap_days))    AS median_gap_days
    FROM gaps AS g
    CROSS JOIN (SELECT count() AS total FROM gaps WHERE gap_days > 0) AS t
    WHERE g.gap_days > 0
    GROUP BY bucket
)
ORDER BY median_gap_days