3,256 answered market questions

WITH paid AS
(
    SELECT
        ex_dividend_date AS ex_date,
        max(cash_amount) AS cash_paid
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('SPYM', 'SPLG')
      AND cash_amount > 0
    GROUP BY ex_dividend_date
),
rolling AS
(
    SELECT
        ex_date,
        sum(cash_paid) OVER (ORDER BY ex_date ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS ttm_cash,
        count()        OVER (ORDER BY ex_date ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS payments_counted
    FROM paid
),
closes AS
(
    SELECT
        date,
        max(close) AS close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SPYM', 'SPLG')
      AND date >= toDate('2015-01-01')
    GROUP BY date
),
recent AS
(
    SELECT
        toString(r.ex_date)                                        AS ex_dividend_date,
        formatDateTime(r.ex_date, '%b %e, %Y')                     AS ex_date_label,
        round(r.ttm_cash, 4)                                       AS ttm_cash_per_share,
        round(toFloat64(c.close), 2)                               AS close_on_ex_date,
        round(100 * toFloat64(r.ttm_cash) / toFloat64(c.close), 2) AS ttm_yield_pct
    FROM rolling AS r
    INNER JOIN closes AS c ON c.date = r.ex_date
    WHERE r.payments_counted = 4
    ORDER BY r.ex_date DESC
    LIMIT 12
)
SELECT *
FROM recent
ORDER BY ex_dividend_date