gap_by_month

SELECT
    toString(m)                                            AS month,
    formatDateTime(m, '%b %Y')                             AS month_label,
    count()                                                AS dividend_count,
    round(avg(dateDiff('day', ex_dt, rec_dt)), 2)          AS avg_calendar_days_ex_to_record,
    round(100 * countIf(rec_dt = ex_dt) / count(), 1)      AS same_day_pct
FROM
(
    SELECT
        toStartOfMonth(any(ex_dividend_date)) AS m,
        any(ex_dividend_date)                 AS ex_dt,
        any(record_date)                      AS rec_dt
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('AAPL', 'MSFT', 'KO', 'JNJ', 'PG', 'PEP', 'JPM', 'XOM', 'CVX', 'HD',
                     'MCD', 'WMT', 'IBM', 'CSCO', 'VZ', 'MRK', 'PFE', 'ABBV', 'TXN', 'LLY')
      AND ex_dividend_date >= '2023-06-01'
      AND ex_dividend_date <  today()
    GROUP BY id
)
WHERE rec_dt >= ex_dt
GROUP BY m
ORDER BY m