3,022 answered market questions

SELECT
    toString(r.ex_date)                                             AS ex_date,
    concat(formatDateTime(r.ex_date, '%b '),
           toString(toDayOfMonth(r.ex_date)), ', ',
           toString(toYear(r.ex_date)))                             AS as_of_label,
    round(r.ttm_per_share, 4)                                       AS ttm_per_share,
    round(100 * r.ttm_per_share / p.close, 2)                       AS ttm_yield_pct
FROM
(
    SELECT
        ex_date,
        sum(amount) OVER (ORDER BY ex_date ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS ttm_per_share,
        count()     OVER (ORDER BY ex_date ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS quarters_counted
    FROM
    (
        SELECT
            ex_dividend_date            AS ex_date,
            toFloat64(max(cash_amount)) AS amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'VOO'
          AND ex_dividend_date <= today()
        GROUP BY ex_dividend_date
    )
) AS r
INNER JOIN
(
    SELECT
        date,
        toFloat64(max(close)) AS close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'VOO'
      AND date >= today() - 1500
    GROUP BY date
) AS p ON p.date = r.ex_date
WHERE r.quarters_counted = 4
  AND r.ex_date >= today() - 1150
ORDER BY r.ex_date