2,249 answered market questions

WITH
monthly AS (
    SELECT toStartOfMonth(dt) AS mo, argMin(c, dt) AS px
    FROM (
        SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS dt,
               argMax(toFloat64(close), window_start) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= '2016-01-01 00:00:00' AND window_start < '2026-07-01 00:00:00'
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
               + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
        GROUP BY dt
    )
    GROUP BY mo
),
agg AS (
    SELECT argMin(px, mo) AS first_px, sum(500.0) AS total_invested FROM monthly
)
SELECT mo AS month,
       round(sum(500.0 / px) OVER (ORDER BY mo) * px / 1000, 1) AS dca_value,
       round((total_invested / first_px) * px / 1000, 1) AS lumpsum_value,
       round(sum(500.0) OVER (ORDER BY mo) / 1000, 1) AS invested
FROM monthly CROSS JOIN agg
ORDER BY mo