3,256 answered market questions

WITH yearly AS
(
    SELECT
        toYear(date)                   AS fiscal_y,
        argMin(toFloat64(close), date) AS first_close,
        argMax(toFloat64(close), date) AS last_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SAP'
      AND date >= '2016-01-01'
      AND date <  '2026-01-01'
    GROUP BY fiscal_y
),
cash AS
(
    SELECT
        toYear(ex_dividend_date)    AS fiscal_y,
        sum(toFloat64(cash_amount)) AS dividends
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SAP'
      AND ex_dividend_date >= '2016-01-01'
      AND ex_dividend_date <  '2026-01-01'
    GROUP BY fiscal_y
)
SELECT
    toString(y.fiscal_y)                                                         AS year,
    round(100 * (y.last_close - y.first_close) / y.first_close, 2)               AS price_return_pct,
    round(100 * c.dividends / y.first_close, 2)                                  AS dividend_pct,
    round(100 * (y.last_close - y.first_close + c.dividends) / y.first_close, 2) AS total_return_pct
FROM yearly AS y
LEFT JOIN cash AS c ON c.fiscal_y = y.fiscal_y
ORDER BY year