배당수익률과 배당성향 차이점 비교

WITH trailing_dividends AS
(
    SELECT
        ticker,
        sum(amount) AS dividend_per_share
    FROM
    (
        SELECT
            ticker,
            id,
            any(toFloat64(cash_amount)) AS amount
        FROM global_markets.stocks_dividends
        WHERE ticker IN ('AAPL', 'ABBV', 'COST', 'CVX', 'HD', 'IBM', 'JNJ', 'KO')
          AND ex_dividend_date >  today() - 365
          AND ex_dividend_date <= today()
          AND frequency > 0
          AND currency = 'USD'
        GROUP BY ticker, id
    )
    GROUP BY ticker
),
latest_ratios AS
(
    SELECT
        ticker,
        argMax(toFloat64(price), date)              AS share_price,
        argMax(toFloat64(earnings_per_share), date) AS eps_ttm
    FROM global_markets.stocks_ratios
    WHERE ticker IN ('AAPL', 'ABBV', 'COST', 'CVX', 'HD', 'IBM', 'JNJ', 'KO')
      AND date >= today() - 45
    GROUP BY ticker
)
SELECT
    d.ticker                                             AS ticker,
    round(100 * d.dividend_per_share / r.share_price, 2) AS dividend_yield_pct,
    round(100 * d.dividend_per_share / r.eps_ttm, 1)     AS payout_ratio_pct
FROM trailing_dividends AS d
INNER JOIN latest_ratios AS r ON r.ticker = d.ticker
WHERE r.share_price > 0
  AND r.eps_ttm > 0
ORDER BY payout_ratio_pct DESC

WITH monthly_close AS
(
    SELECT
        toStartOfMonth(date)           AS month_start,
        argMax(toFloat64(close), date) AS month_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'KO'
      AND date >= toStartOfMonth(today() - 1095)
      AND date <= today()
    GROUP BY month_start
),
ko_dividends AS
(
    SELECT
        ex_dividend_date            AS ex_date,
        any(toFloat64(cash_amount)) AS amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'KO'
      AND ex_dividend_date > today() - 1500
      AND frequency > 0
    GROUP BY id, ex_dividend_date
)
SELECT
    toString(m.month_start)                            AS month,
    formatDateTime(m.month_start, '%b %Y')             AS month_label,
    round(sum(d.amount), 4)                            AS trailing_dividend_per_share,
    round(100 * sum(d.amount) / any(m.month_close), 2) AS dividend_yield_pct
FROM monthly_close AS m
CROSS JOIN ko_dividends AS d
WHERE d.ex_date <= toLastDayOfMonth(m.month_start)
  AND d.ex_date >  toLastDayOfMonth(m.month_start) - 365
GROUP BY m.month_start
ORDER BY m.month_start

WITH latest_ratios AS
(
    SELECT
        ticker,
        argMax(toFloat64(price), date)              AS share_price,
        argMax(toFloat64(earnings_per_share), date) AS eps_ttm,
        argMax(toFloat64(market_cap), date)         AS market_value
    FROM global_markets.stocks_ratios
    WHERE date >= today() - 30
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
),
trailing_dividends AS
(
    SELECT
        ticker,
        sum(amount) AS dividend_per_share
    FROM
    (
        SELECT
            ticker,
            id,
            any(toFloat64(cash_amount)) AS amount
        FROM global_markets.stocks_dividends
        WHERE ex_dividend_date >  today() - 365
          AND ex_dividend_date <= today()
          AND frequency > 0
          AND currency = 'USD'
        GROUP BY ticker, id
    )
    GROUP BY ticker
)
SELECT
    r.ticker                                             AS ticker,
    round(100 * d.dividend_per_share / r.share_price, 2) AS dividend_yield_pct,
    round(100 * d.dividend_per_share / r.eps_ttm, 0)     AS payout_ratio_pct
FROM latest_ratios AS r
INNER JOIN trailing_dividends AS d ON d.ticker = r.ticker
WHERE r.market_value > 5000000000
  AND r.eps_ttm > 0
  AND r.share_price > 1
  AND d.dividend_per_share / r.eps_ttm > 1
  AND d.dividend_per_share / r.share_price < 0.30
ORDER BY dividend_yield_pct DESC
LIMIT 12

SELECT
    toString(toYear(period_end))                    AS year,
    round(100 * dividends_paid / net_profit, 1)     AS payout_of_earnings_pct,
    round(100 * dividends_paid / free_cash_flow, 1) AS payout_of_free_cash_flow_pct
FROM
(
    SELECT
        period_end,
        abs(toFloat64(argMax(dividends, (filing_date, period_end))))                     AS dividends_paid,
        toFloat64(argMax(net_income, (filing_date, period_end)))                         AS net_profit,
        toFloat64(argMax(net_cash_from_operating_activities, (filing_date, period_end)))
            - abs(toFloat64(argMax(purchase_of_property_plant_and_equipment, (filing_date, period_end)))) AS free_cash_flow
    FROM global_markets.stocks_cash_flow_statements
    WHERE has(tickers, 'KO')
      AND timeframe = 'annual'
      AND period_end >= '2017-12-01'
    GROUP BY period_end
)
WHERE dividends_paid > 0
  AND net_profit > 0
  AND free_cash_flow > 0
ORDER BY period_end