Average distribution against the average ex-date price move

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
    weekly AS
    (
        SELECT ticker
        FROM global_markets.stocks_dividends
        WHERE ex_dividend_date >= today() - 365
          AND ex_dividend_date <= today()
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING countDistinct(ex_dividend_date) >= 40
    ),
    payments AS
    (
        SELECT
            ticker,
            ex_dividend_date  AS ex_date,
            max(cash_amount)  AS amount
        FROM global_markets.stocks_dividends
        WHERE ex_dividend_date >= today() - 365
          AND ex_dividend_date <= today()
          AND ticker IN (SELECT ticker FROM weekly)
        GROUP BY ticker, ex_date
    ),
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(any(close)) AS close
        FROM global_markets.stocks_daily_aggs
        WHERE date >= today() - 400
          AND ticker IN (SELECT ticker FROM weekly)
        GROUP BY ticker, date
    )
SELECT
    ticker,
    count()                                AS ex_dates,
    round(avg(amount), 4)                  AS avg_distribution,
    round(avg(ex_close - prior_close), 4)  AS avg_overnight_move
FROM
(
    SELECT
        d.ticker                   AS ticker,
        d.ex_date                  AS ex_date,
        toFloat64(any(d.amount))   AS amount,
        any(pe.close)              AS ex_close,
        argMax(pp.close, pp.date)  AS prior_close
    FROM payments AS d
    INNER JOIN px AS pe ON pe.ticker = d.ticker AND pe.date = d.ex_date
    INNER JOIN px AS pp ON pp.ticker = d.ticker
    WHERE pp.date <  d.ex_date
      AND pp.date >= d.ex_date - 7
    GROUP BY d.ticker, d.ex_date
)
GROUP BY ticker
ORDER BY avg_distribution DESC
LIMIT 10