Increases against cuts, by week of declaration (complete weeks only)

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 universe AS (
    SELECT ticker, argMax(market_cap, date) AS mcap
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 10 AND market_cap > 0
    GROUP BY ticker
),
regular AS (
    SELECT ticker,
           ex_dividend_date,
           max(declaration_date) AS declared_on,
           argMax(cash_amount, declaration_date) AS amt,
           argMax(frequency, declaration_date) AS freq
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND cash_amount > 0
      AND currency = 'USD'
      AND frequency IN (1, 2, 4, 12)
      AND ex_dividend_date >= today() - 1200
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, ex_dividend_date
),
seq AS (
    SELECT ticker, ex_dividend_date, declared_on, amt, freq,
           lagInFrame(amt) OVER w AS prev_amt,
           lagInFrame(freq) OVER w AS prev_freq,
           lagInFrame(ex_dividend_date) OVER w AS prev_ex
    FROM regular
    WINDOW w AS (PARTITION BY ticker ORDER BY ex_dividend_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
),
splits AS (
    SELECT ticker, groupArray(execution_date) AS split_dates
    FROM global_markets.stocks_splits
    WHERE execution_date >= today() - 1300
    GROUP BY ticker
)
SELECT toString(toMonday(s.declared_on)) AS week,
       formatDateTime(toMonday(s.declared_on), '%b %e') AS week_label,
       countIf(s.amt > s.prev_amt * 1.001) AS increases,
       countIf(s.amt < s.prev_amt * 0.995) AS cuts
FROM seq s
INNER JOIN universe u ON u.ticker = s.ticker
LEFT JOIN splits sp ON sp.ticker = s.ticker
WHERE s.prev_amt > 0
  AND s.freq = s.prev_freq
  AND arrayCount(x -> x > s.prev_ex, sp.split_dates) = 0
  AND dateDiff('day', s.prev_ex, s.ex_dividend_date) BETWEEN intDiv(240, s.freq) AND intDiv(520, s.freq)
  AND s.declared_on >= toMonday(today()) - 182
  AND s.declared_on < toMonday(today())
GROUP BY week, week_label
ORDER BY week