配当取り戦略は本当に機能するのか

WITH tk AS (SELECT ['KO','JNJ','PG','XOM','CVX','VZ','MRK','PEP','MCD','MMM'] AS t),
px AS (
    SELECT ticker,
           date,
           toFloat64(open) AS day_open,
           lagInFrame(toFloat64(close)) OVER (PARTITION BY ticker ORDER BY date
                                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN (SELECT arrayJoin(t) FROM tk)
      AND date BETWEEN toDate('2023-06-01') AND toDate('2026-06-30')
      AND open > 0 AND close > 0
),
dv AS (
    SELECT ticker, ex_dividend_date AS d, max(cash_amount) AS div_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN (SELECT arrayJoin(t) FROM tk)
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date BETWEEN toDate('2023-07-01') AND toDate('2026-06-30')
    GROUP BY ticker, d
)
SELECT px.ticker AS ticker,
       count() AS ex_dates,
       round(avg(dv.div_amount), 3) AS avg_dividend_usd,
       round(avg(px.prev_close - px.day_open), 3) AS avg_overnight_decline_usd,
       round(avg(px.prev_close - px.day_open) / avg(dv.div_amount), 2) AS decline_per_dividend_ratio
FROM px
INNER JOIN dv ON px.ticker = dv.ticker AND px.date = dv.d
WHERE px.prev_close > 0
GROUP BY px.ticker
ORDER BY decline_per_dividend_ratio DESC

WITH divs AS (
    SELECT ticker, ex_dividend_date AS d, max(cash_amount) AS div_amount
    FROM global_markets.stocks_dividends
    WHERE distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date BETWEEN toDate('2024-01-01') AND toDate('2026-06-30')
    GROUP BY ticker, d
),
px AS (
    SELECT ticker,
           date,
           toFloat64(open) AS day_open,
           lagInFrame(toFloat64(close)) OVER (PARTITION BY ticker ORDER BY date
                                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM global_markets.stocks_daily_aggs
    WHERE date BETWEEN toDate('2023-12-01') AND toDate('2026-06-30')
      AND open > 0 AND close > 0
),
ev AS (
    SELECT (px.prev_close - px.day_open) / divs.div_amount AS ratio
    FROM px
    INNER JOIN divs ON px.ticker = divs.ticker AND px.date = divs.d
    WHERE px.prev_close >= 5
      AND divs.div_amount / px.prev_close BETWEEN 0.001 AND 0.05
)
SELECT multiIf(ratio < 0, 'opened higher',
               ratio < 0.5, 'fell under half',
               ratio < 1, 'fell half to full',
               ratio < 1.5, 'fell 1 to 1.5x',
               'fell over 1.5x') AS drop_bucket,
       count() AS ex_dates,
       round(100 * count() / (SELECT count() FROM ev), 1) AS share_pct
FROM ev
GROUP BY drop_bucket
ORDER BY multiIf(drop_bucket = 'opened higher', 0,
                 drop_bucket = 'fell under half', 1,
                 drop_bucket = 'fell half to full', 2,
                 drop_bucket = 'fell 1 to 1.5x', 3, 4)

WITH px AS (
    SELECT date,
           toFloat64(open) AS day_open,
           lagInFrame(toFloat64(close)) OVER (ORDER BY date
                                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'KO'
      AND date BETWEEN toDate('2023-06-01') AND toDate('2026-06-30')
      AND open > 0 AND close > 0
),
dv AS (
    SELECT ex_dividend_date AS d, max(cash_amount) AS div_amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'KO'
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date BETWEEN toDate('2023-07-01') AND toDate('2026-06-30')
    GROUP BY d
)
SELECT formatDateTime(px.date, '%Y-%m-%d') AS date,
       round(dv.div_amount, 3) AS dividend_usd,
       round(px.prev_close - px.day_open, 3) AS overnight_decline_usd,
       round((px.prev_close - px.day_open) - dv.div_amount, 3) AS decline_minus_dividend_usd
FROM px
INNER JOIN dv ON px.date = dv.d
ORDER BY date

WITH tk AS (SELECT ['KO','JNJ','PG','XOM','CVX','VZ','MRK','PEP','MCD','MMM'] AS t),
r AS (
    SELECT ticker,
           date,
           toFloat64(close) AS close,
           lagInFrame(toFloat64(close)) OVER (PARTITION BY ticker ORDER BY date
                                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN (SELECT arrayJoin(t) FROM tk)
      AND date BETWEEN toDate('2023-07-01') AND toDate('2026-06-30')
      AND close > 0
),
vol AS (
    SELECT ticker,
           round(100 * avg(abs(close / prev_close - 1)), 2) AS avg_daily_move_pct,
           round(quantileDeterministic(0.95)(100 * abs(close / prev_close - 1), cityHash64(date)), 2) AS p95_daily_move_pct,
           avg(close) AS avg_close
    FROM r
    WHERE prev_close > 0
    GROUP BY ticker
),
dv AS (
    SELECT ticker, avg(cash_amount) AS avg_dividend_usd
    FROM global_markets.stocks_dividends
    WHERE ticker IN (SELECT arrayJoin(t) FROM tk)
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date BETWEEN toDate('2023-07-01') AND toDate('2026-06-30')
    GROUP BY ticker
)
SELECT vol.ticker AS ticker,
       round(100 * dv.avg_dividend_usd / vol.avg_close, 2) AS dividend_pct_of_price,
       vol.avg_daily_move_pct AS avg_daily_move_pct,
       vol.p95_daily_move_pct AS p95_daily_move_pct
FROM vol
INNER JOIN dv ON vol.ticker = dv.ticker
ORDER BY dividend_pct_of_price DESC