מתי חברות מכריזות על העלאות דיבידנד?

WITH quarterly AS (
    SELECT ticker,
           ex_dividend_date,
           round(max(toFloat64(cash_amount)), 4) AS amount,
           max(declaration_date) AS declared
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND declaration_date > toDate('2015-06-30')
      AND ex_dividend_date >= toDate('2015-07-01')
      AND ex_dividend_date <= toDate('2026-07-31')
    GROUP BY ticker, ex_dividend_date
),
stepped AS (
    SELECT ticker,
           declared,
           amount,
           ex_dividend_date,
           lagInFrame(amount, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_amount,
           lagInFrame(ex_dividend_date, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_ex_date
    FROM quarterly
)
SELECT formatDateTimeInJodaSyntax(declared, 'MMM') AS month_label,
       count() AS raises,
       uniqExact(ticker) AS companies
FROM stepped
WHERE prior_amount > 0
  AND amount > prior_amount * 1.005
  AND dateDiff('day', prior_ex_date, ex_dividend_date) BETWEEN 45 AND 200
GROUP BY month_label
ORDER BY raises DESC

WITH quarterly AS (
    SELECT ticker,
           ex_dividend_date,
           round(max(toFloat64(cash_amount)), 4) AS amount,
           max(declaration_date) AS declared
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('JNJ','KO','PG','PEP','MCD','WMT','CVX','XOM','MMM','CAT','HD','ADP')
      AND distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND declaration_date > toDate('2015-06-30')
      AND ex_dividend_date >= toDate('2015-07-01')
      AND ex_dividend_date <= toDate('2026-07-31')
    GROUP BY ticker, ex_dividend_date
),
stepped AS (
    SELECT ticker,
           declared,
           amount,
           ex_dividend_date,
           lagInFrame(amount, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_amount,
           lagInFrame(ex_dividend_date, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_ex_date
    FROM quarterly
),
raises AS (
    SELECT ticker,
           formatDateTimeInJodaSyntax(declared, 'MMM') AS month_label,
           toMonth(declared) AS month_index
    FROM stepped
    WHERE prior_amount > 0
      AND amount > prior_amount * 1.005
      AND dateDiff('day', prior_ex_date, ex_dividend_date) BETWEEN 45 AND 200
),
per_month AS (
    SELECT ticker,
           month_label,
           month_index,
           count() AS raises_in_month
    FROM raises
    GROUP BY ticker, month_label, month_index
)
SELECT ticker,
       argMax(month_label, raises_in_month * 100 + month_index) AS typical_month,
       sum(raises_in_month) AS raises_total,
       max(raises_in_month) AS raises_in_typical_month,
       round(100 * max(raises_in_month) / sum(raises_in_month)) AS same_month_pct
FROM per_month
GROUP BY ticker
HAVING sum(raises_in_month) >= 5
ORDER BY same_month_pct DESC, raises_total DESC

WITH quarterly AS (
    SELECT ticker,
           ex_dividend_date,
           round(max(toFloat64(cash_amount)), 4) AS amount,
           max(declaration_date) AS declared
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND declaration_date > toDate('2015-06-30')
      AND ex_dividend_date >= toDate('2015-07-01')
      AND ex_dividend_date <= toDate('2026-07-31')
    GROUP BY ticker, ex_dividend_date
),
stepped AS (
    SELECT ticker,
           declared,
           amount,
           ex_dividend_date,
           lagInFrame(amount, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_amount,
           lagInFrame(ex_dividend_date, 1) OVER (PARTITION BY ticker ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_ex_date
    FROM quarterly
),
raises AS (
    SELECT ticker, declared
    FROM stepped
    WHERE prior_amount > 0
      AND amount > prior_amount * 1.005
      AND dateDiff('day', prior_ex_date, ex_dividend_date) BETWEEN 45 AND 200
),
chained AS (
    SELECT ticker,
           declared,
           lagInFrame(declared, 1) OVER (PARTITION BY ticker ORDER BY declared ASC
                                         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_declared
    FROM raises
)
SELECT toYear(declared) AS year,
       uniqExact(ticker) AS companies,
       round(100 * countIf(toMonth(declared) = toMonth(prior_declared)) / count(), 1) AS same_month_pct,
       round(100 * countIf(abs(toInt32(toRelativeMonthNum(declared))
                               - toInt32(toRelativeMonthNum(prior_declared)) - 12) <= 1) / count(), 1) AS within_one_month_pct
FROM chained
WHERE declared >= toDate('2017-01-01')
  AND dateDiff('day', prior_declared, declared) BETWEEN 250 AND 480
GROUP BY year
ORDER BY year

WITH quarterly AS (
    SELECT ex_dividend_date,
           round(max(toFloat64(cash_amount)), 4) AS amount,
           max(declaration_date) AS declared
    FROM global_markets.stocks_dividends
    WHERE ticker = 'JNJ'
      AND distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND declaration_date > toDate('2015-06-30')
      AND ex_dividend_date >= toDate('2015-07-01')
      AND ex_dividend_date <= toDate('2026-07-31')
    GROUP BY ex_dividend_date
),
stepped AS (
    SELECT declared,
           amount,
           ex_dividend_date,
           lagInFrame(amount, 1) OVER (ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_amount,
           lagInFrame(ex_dividend_date, 1) OVER (ORDER BY ex_dividend_date ASC
                                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_ex_date
    FROM quarterly
)
SELECT toYear(declared) AS year,
       formatDateTimeInJodaSyntax(declared, 'MMM d') AS declared_on,
       round(prior_amount, 4) AS old_quarterly_usd,
       round(amount, 4) AS new_quarterly_usd,
       round(100 * (amount / prior_amount - 1), 1) AS raise_pct
FROM stepped
WHERE prior_amount > 0
  AND amount > prior_amount * 1.005
  AND dateDiff('day', prior_ex_date, ex_dividend_date) BETWEEN 45 AND 200
ORDER BY year

SELECT toYear(declaration_date) AS year,
       uniqExact(ticker) AS company_count,
       count() AS declaration_count,
       round(100 * countIf(declaration_date <= ex_dividend_date) / count(), 1) AS declared_before_ex_pct,
       round(quantileDeterministic(0.5)(dateDiff('day', declaration_date, ex_dividend_date),
                                        cityHash64(ticker))) AS median_days_declared_to_ex
FROM global_markets.stocks_dividends
WHERE distribution_type = 'recurring'
  AND cash_amount > 0
  AND declaration_date > toDate('2015-12-31')
  AND declaration_date <= toDate('2026-07-31')
  AND ex_dividend_date > toDate('2015-12-31')
GROUP BY year
ORDER BY year