3,256 answered market questions

WITH dated AS
(
    SELECT
        ticker,
        ex_dividend_date,
        max(frequency) AS freq
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= today() - 800
      AND ex_dividend_date <  today()
      AND frequency IN (1, 2, 4, 12)
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, ex_dividend_date
),
gaps AS
(
    SELECT
        freq,
        dateDiff('day',
                 lagInFrame(ex_dividend_date) OVER (PARTITION BY ticker
                                                    ORDER BY ex_dividend_date ASC
                                                    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
                 ex_dividend_date) AS days_between
    FROM dated
)
SELECT
    multiIf(freq = 12, 'Monthly',
            freq = 4,  'Quarterly',
            freq = 2,  'Semi-annual',
                       'Annual')    AS label,
    round(avg(days_between), 1)     AS avg_days_between,
    count()                         AS gap_count
FROM gaps
WHERE days_between BETWEEN 10 AND 450
GROUP BY freq
ORDER BY avg_days_between