rolling_7y

WITH
    monthly AS
    (
        SELECT
            toStartOfMonth(date)            AS month_start,
            toDate(min(date))               AS first_session,
            argMin(toFloat64(close), date)  AS first_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
        GROUP BY month_start
    ),
    divs AS
    (
        SELECT
            toStartOfMonth(ex_dividend_date) AS month_start,
            sum(cash)                        AS month_cash
        FROM
        (
            SELECT
                ex_dividend_date,
                max(toFloat64(cash_amount)) AS cash
            FROM global_markets.stocks_dividends
            WHERE ticker = 'SPY'
            GROUP BY ex_dividend_date
        )
        GROUP BY month_start
    ),
    grid AS
    (
        SELECT
            m.month_start                AS month_start,
            m.first_session              AS first_session,
            m.first_close                AS first_close,
            addMonths(m.month_start, 84) AS end_month,
            sum(ifNull(d.month_cash, 0)) OVER (ORDER BY m.month_start ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS cash_before
        FROM monthly AS m
        LEFT JOIN divs AS d ON d.month_start = m.month_start
    )
SELECT
    toString(s.first_session)                            AS start_date,
    formatDateTime(s.first_session, '%b %Y')             AS opened,
    formatDateTime(e.first_session, '%b %Y')             AS closed,
    round((e.first_close / s.first_close - 1) * 100, 1)  AS price_return_pct,
    round(((e.first_close + e.cash_before - s.cash_before) / s.first_close - 1) * 100, 1) AS with_dividends_pct
FROM grid AS s
INNER JOIN grid AS e ON e.month_start = s.end_month
ORDER BY s.month_start