DCA vs Lump-Sum

A side-by-side monthly running comparison of two contribution strategies on the same total budget:

• DCA (Dollar-Cost Averaging) — invest a fixed $1000 at the start of each month, accumulating BTC at whatever the price is that month.

• Lump-sum — at month T, this represents "what if I had instead invested all T × $1000 at month 0's price?". Useful as a baseline to read off DCA's smoothing-vs-opportunity-cost trade-off at a glance. 12-31to match the parity-tested baseline; for live data swap the two date literals for a relative window likenow() - interval 5 year`.

Three SQL details worth flagging:

Query

WITH
    '2020-01-01' AS start_date,
    '2024-12-31' AS end_date,
    1000 AS monthly_contribution,
    monthly AS (
        SELECT toStartOfMonth(toDate(start)) AS month,
               argMin(toFloat64(close), start) AS open_price
        FROM api.ohlcv(candle_duration_in_minutes = 1440)
        WHERE exchange = 'binance'
          AND market = 'BTC-USDT'
          AND start BETWEEN start_date AND end_date
        GROUP BY month
    )
SELECT month,
       open_price,
       sum(monthly_contribution) OVER w AS cumulative_invested,
       sum(monthly_contribution / open_price) OVER w AS dca_btc,
       sum(monthly_contribution / open_price) OVER w * open_price AS dca_portfolio_value,
       sum(monthly_contribution) OVER w / first_value(open_price) OVER w AS lumpsum_btc,
       sum(monthly_contribution) OVER w / first_value(open_price) OVER w * open_price AS lumpsum_portfolio_value
FROM monthly
WINDOW w AS (ORDER BY month)
ORDER BY month

Functions used: toStartOfMonth, toDate, argMin, toFloat64, window functions (first_value/sum OVER)

Three SQL details worth flagging:

• Monthly bucket via toStartOfMonth(toDate(start)) + argMin(close, start) picks the first daily close in each calendar month — same as groupby('month').first() in pandas. We buy and value at the same monthly open price, so the latest month's row honestly shows zero return on its just-purchased contribution; earlier rows reflect the appreciation since that month.

• DCA accumulates via sum(monthly_contribution / open_price) OVER w — one running sum, not a manual loop. Multiply by the current month's open to mark-to-market.

• Lump-sum is anchored to month 0's price via first_value(open_price) OVER w. At every row, cumulative_invested / first_open_price gives the BTC count you'd hold if you'd put the whole running total in at the start.

Output (2020 — 2024, 60 monthly rows)

First and last few:

month	open_price	cumulative_invested	dca_btc	dca_portfolio_value	lumpsum_btc	lumpsum_portfolio_value
2020-01-01	7200.85	1,000	0.14	1,000.00	0.14	1,000.00
2020-02-01	9384.61	2,000	0.25	2,303.26	0.28	2,606.53
2020-03-01	8531.88	3,000	0.36	3,093.98	0.42	3,554.53
...	...	...	...	...	...	...
2024-10-01	60805.78	58,000	2.57	156,432.34	8.05	489,766.52
2024-11-01	69496.01	59,000	2.59	179,789.31	8.19	569,413.97
2024-12-01	97185.18	60,000	2.60	252,422.44	8.33	809,780.90

After 60 months on BTC-USDT:

	Final BTC	Final value	Return on $60k
DCA	2.60	$ 252,422	+320.7 %
Lump-sum	8.33	$ 809,781	+1,249.6 %

Extending it

• Different cadence — change toStartOfMonth(...) to toStartOfWeek(...) for weekly DCA, or toStartOfQuarter(...) for quarterly. The contribution amount adjusts to match.

• Different basket — the SQL pattern is single-asset; for a multi-asset DCA wrap the inner CTE with a PARTITION BY market on every window function and add market to the final ORDER BY.

• Variable contributions — replace the constant 1000 AS monthly_contribution with a join against a table or values list of (month, contribution) to model irregular schedules.