월간 배당주 설명

SELECT multiIf(freq = 4, 'Quarterly',
               freq = 2, 'Twice a year',
               freq = 1, 'Once a year',
               freq = 12, 'Monthly',
               'Other schedule') AS schedule,
       uniqExact(ticker) AS tickers,
       count() AS payments,
       round(count() / uniqExact(ticker), 1) AS payments_per_ticker,
       round(100 * uniqExact(ticker) / (SELECT uniqExact(ticker)
                                        FROM global_markets.stocks_dividends
                                        WHERE ex_dividend_date >= '2025-07-01'
                                          AND ex_dividend_date <= '2026-06-30'
                                          AND cash_amount > 0
                                          AND distribution_type = 'recurring'), 1) AS share_of_payers_pct
FROM (
    SELECT ticker,
           ex_dividend_date,
           argMax(frequency, ex_dividend_date) OVER (PARTITION BY ticker) AS freq
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= '2025-07-01'
      AND ex_dividend_date <= '2026-06-30'
      AND cash_amount > 0
      AND distribution_type = 'recurring'
)
GROUP BY schedule
ORDER BY tickers DESC

WITH sched AS (
    SELECT ticker,
           argMax(frequency, ex_dividend_date) AS freq,
           sum(cash_amount) AS ttm_cash
    FROM global_markets.stocks_dividends
    WHERE cash_amount > 0
      AND distribution_type = 'recurring'
      AND ex_dividend_date >= '2025-07-01'
      AND ex_dividend_date <= '2026-06-30'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING freq IN (1, 2, 4, 12)
),
tape AS (
    SELECT ticker,
           argMax(toFloat64(close), window_start) AS last_close,
           sum(toFloat64(close) * volume) AS dollar_vol
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM sched)
      AND window_start >= toDateTime('2026-06-01 00:00:00')
      AND window_start < toDateTime('2026-07-01 00:00:00')
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) BETWEEN 570 AND 959
    GROUP BY ticker
    HAVING last_close > 5 AND dollar_vol >= 20000000
)
SELECT multiIf(freq = 12, 'Monthly',
               freq = 4, 'Quarterly',
               freq = 2, 'Twice a year',
               'Once a year') AS schedule,
       count() AS tickers,
       round(quantileDeterministic(0.5)(100 * ttm_cash / last_close, cityHash64(ticker)), 2) AS median_trailing_yield_pct,
       round(quantileDeterministic(0.9)(100 * ttm_cash / last_close, cityHash64(ticker)), 2) AS p90_trailing_yield_pct
FROM sched
INNER JOIN tape USING (ticker)
GROUP BY schedule
ORDER BY median_trailing_yield_pct DESC

WITH base AS (
    SELECT toStartOfMonth(pay_date) AS m,
           ifNull(sumIf(cash_amount, ticker = 'O'), 0) AS o_cash,
           ifNull(sumIf(cash_amount, ticker = 'KO'), 0) AS ko_cash
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('O', 'KO')
      AND cash_amount > 0
      AND distribution_type = 'recurring'
      AND pay_date >= '2025-01-01'
      AND pay_date <= '2025-12-31'
    GROUP BY m
)
SELECT formatDateTime(m, '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(m, 'MMMM') AS month_label,
       round(100 * o_cash / sum(o_cash) OVER (), 1) AS monthly_payer_pct_of_year,
       round(100 * ko_cash / sum(ko_cash) OVER (), 1) AS quarterly_payer_pct_of_year
FROM base
ORDER BY m

WITH monthly AS (
    SELECT ticker,
           argMaxIf(cash_amount, ex_dividend_date, ex_dividend_date >= '2025-07-01') AS recent_rate,
           argMinIf(cash_amount, ex_dividend_date, ex_dividend_date <= '2023-12-31') AS early_rate
    FROM global_markets.stocks_dividends
    WHERE cash_amount > 0
      AND distribution_type = 'recurring'
      AND ex_dividend_date >= '2023-07-01'
      AND ex_dividend_date <= '2026-06-30'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING countIf(ex_dividend_date >= '2025-07-01') BETWEEN 10 AND 14
       AND argMax(frequency, ex_dividend_date) = 12
       AND countIf(ex_dividend_date <= '2023-12-31') >= 4
       AND early_rate > 0
),
tape AS (
    SELECT ticker,
           argMax(toFloat64(close), window_start) AS last_close,
           sum(toFloat64(close) * volume) AS dollar_vol
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM monthly)
      AND window_start >= toDateTime('2026-06-01 00:00:00')
      AND window_start < toDateTime('2026-07-01 00:00:00')
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) BETWEEN 570 AND 959
    GROUP BY ticker
    HAVING last_close > 0 AND dollar_vol >= 20000000
)
SELECT multiIf(100 * (recent_rate - early_rate) / early_rate < -5, 'Rate cut',
               100 * (recent_rate - early_rate) / early_rate <= 5, 'Rate held flat',
               100 * (recent_rate - early_rate) / early_rate <= 25, 'Rate raised up to 25%',
               'Rate raised over 25%') AS rate_direction,
       count() AS payers,
       round(100 * count() / sum(count()) OVER (), 1) AS share_of_group_pct,
       round(quantileDeterministic(0.5)(100 * recent_rate * 12 / last_close, cityHash64(ticker)), 2) AS median_yield_pct,
       round(quantileDeterministic(0.5)(100 * (recent_rate - early_rate) / early_rate, cityHash64(ticker)), 1) AS median_rate_change_pct
FROM monthly
INNER JOIN tape USING (ticker)
GROUP BY rate_direction
ORDER BY median_rate_change_pct