מלכודת תשואת דיבידנד: איך לזהות אותה

WITH px_base AS (
    SELECT ticker,
           argMax(toFloat64(close), window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2025-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker
    HAVING price >= 5
),
px_prior AS (
    SELECT ticker,
           argMax(toFloat64(close), window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2024-06-28')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker
    HAVING price > 0
),
rate_base AS (
    SELECT ticker,
           argMax(toFloat64(cash_amount) * frequency, ex_dividend_date) AS annual_rate
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date > toDate('2024-06-30')
      AND ex_dividend_date <= toDate('2025-06-30')
    GROUP BY ticker
),
rate_prior AS (
    SELECT ticker,
           argMax(toFloat64(cash_amount) * frequency, ex_dividend_date) AS annual_rate
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date > toDate('2023-06-30')
      AND ex_dividend_date <= toDate('2024-06-28')
    GROUP BY ticker
)
SELECT multiIf(100 * rb.annual_rate / b.price >= 8, '8% and up',
               100 * rb.annual_rate / b.price >= 6, '6-8%',
               100 * rb.annual_rate / b.price >= 4, '4-6%',
               100 * rb.annual_rate / b.price >= 2, '2-4%',
               'under 2%') AS yield_band,
       count() AS names,
       round(quantileDeterministic(0.5)(100 * rb.annual_rate / b.price, cityHash64(b.ticker)), 2) AS median_yield_pct,
       round(quantileDeterministic(0.5)(100 * (b.price / p.price - 1), cityHash64(b.ticker)), 1) AS median_price_change_pct,
       round(quantileDeterministic(0.5)(100 * (rb.annual_rate / rp.annual_rate - 1), cityHash64(b.ticker)), 1) AS median_dividend_change_pct
FROM px_base AS b
INNER JOIN px_prior AS p ON b.ticker = p.ticker
INNER JOIN rate_base AS rb ON b.ticker = rb.ticker
INNER JOIN rate_prior AS rp ON b.ticker = rp.ticker
GROUP BY yield_band
ORDER BY median_yield_pct

WITH px_base AS (
    SELECT ticker,
           argMax(toFloat64(close), window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2025-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker
    HAVING price >= 5
),
rate_before AS (
    SELECT ticker,
           argMax(toFloat64(cash_amount) * frequency, ex_dividend_date) AS annual_rate
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date > toDate('2024-06-30')
      AND ex_dividend_date <= toDate('2025-06-30')
    GROUP BY ticker
),
rate_after AS (
    SELECT ticker,
           argMax(toFloat64(cash_amount) * frequency, ex_dividend_date) AS annual_rate
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency > 0
      AND cash_amount > 0
      AND ex_dividend_date > toDate('2025-06-30')
      AND ex_dividend_date <= toDate('2026-06-30')
    GROUP BY ticker
)
SELECT multiIf(100 * b.annual_rate / p.price >= 8, '8% and up',
               100 * b.annual_rate / p.price >= 6, '6-8%',
               100 * b.annual_rate / p.price >= 4, '4-6%',
               100 * b.annual_rate / p.price >= 2, '2-4%',
               'under 2%') AS yield_band,
       count() AS payers,
       round(quantileDeterministic(0.5)(100 * b.annual_rate / p.price, cityHash64(p.ticker)), 2) AS median_yield_pct,
       round(100 * countIf(ifNull(a.annual_rate, 0) = 0) / count(), 1) AS pct_stopped_paying,
       round(100 * countIf(ifNull(a.annual_rate, 0) > 0 AND a.annual_rate < b.annual_rate * 0.99) / count(), 1) AS pct_reduced_rate,
       round(100 * countIf(ifNull(a.annual_rate, 0) = 0 OR a.annual_rate < b.annual_rate * 0.99) / count(), 1) AS pct_reduced_or_stopped,
       round(100 * countIf(ifNull(a.annual_rate, 0) >= b.annual_rate * 0.99) / count(), 1) AS pct_maintained_or_raised
FROM px_base AS p
INNER JOIN rate_before AS b ON p.ticker = b.ticker
LEFT JOIN rate_after AS a ON p.ticker = a.ticker
GROUP BY yield_band
ORDER BY median_yield_pct

SELECT multiIf(dividend_yield * 100 >= 8, '8% and up',
               dividend_yield * 100 >= 6, '6-8%',
               dividend_yield * 100 >= 4, '4-6%',
               dividend_yield * 100 >= 2, '2-4%',
               'under 2%') AS yield_band,
       count() AS payers,
       round(quantileDeterministic(0.5)(dividend_yield * 100, cityHash64(ticker)), 2) AS median_yield_pct,
       round(quantileDeterministicIf(0.5)(dividend_yield * price / earnings_per_share * 100,
                                          cityHash64(ticker), earnings_per_share > 0), 1) AS median_payout_ratio_pct,
       round(100 * countIf(earnings_per_share > 0 AND dividend_yield * price > earnings_per_share)
             / countIf(earnings_per_share > 0), 1) AS pct_paying_over_earnings,
       round(100 * countIf(earnings_per_share <= 0) / count(), 1) AS pct_no_positive_eps
FROM global_markets.stocks_ratios
WHERE date = (SELECT max(date) FROM global_markets.stocks_ratios)
  AND price >= 5
  AND market_cap >= 1000000000
  AND dividend_yield > 0
  AND earnings_per_share IS NOT NULL
GROUP BY yield_band
HAVING countIf(earnings_per_share > 0) > 0
ORDER BY median_yield_pct

WITH px AS (
    SELECT toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
           argMax(toFloat64(close), window_start) AS price,
           max(toDate(toTimeZone(window_start, 'America/New_York'))) AS last_day
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'CAG'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2021-08-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY month_start
),
dv AS (
    SELECT ex_dividend_date, toFloat64(cash_amount) AS cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'CAG'
      AND distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2021-01-01')
)
SELECT formatDateTime(px.month_start, '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(px.month_start, 'MMMM yyyy') AS month_label,
       round(any(px.price), 2) AS price_usd,
       round(argMax(dv.cash_amount, dv.ex_dividend_date) * 4, 2) AS annual_dividend_usd,
       round(argMax(dv.cash_amount, dv.ex_dividend_date) * 4 / any(px.price) * 100, 2) AS dividend_yield_pct
FROM px, dv
WHERE dv.ex_dividend_date <= px.last_day
GROUP BY px.month_start
ORDER BY px.month_start