> For the complete documentation index, see [llms.txt](https://docs.koinju.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.koinju.io/sql-cookbook/dca-vs-lump-sum.md).

# 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-31`to match the parity-tested baseline; for live data swap the two date literals for a relative window like`now() - interval 5 year\`.

Three SQL details worth flagging:

## Query

```sql
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`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofmonth), [`toDate`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todate), [`argMin`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmin), [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tofloat64), [window functions (`first_value`/`sum` OVER)](https://clickhouse.com/docs/sql-reference/window-functions#first_value)

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.