I wrote 400 lines to avoid learning one SQL feature

TL;DR#

I needed each customer's most recent order, plus their rank by lifetime value, plus a running total. I knew how to write that in Kotlin, and I did not know window functions — so I pulled 180,000 rows into the application and wrote 400 lines of grouping, sorting and accumulating.

It worked, it was tested, and it took 14 seconds and 600MB of heap. The SQL equivalent is eleven lines, runs in 230 milliseconds, and I learned it in about two hours.

The 400 lines were not a technical decision. They were the cost of avoiding two hours of learning, paid forward as code I then had to maintain — and I made the same trade twice more before noticing the pattern.

The case this post is about#

The requirement: a customer report with, per customer, their latest order, their rank by lifetime spend, and the running total of spend across the ranked list.

What I wrote:

// 400 lines, abridged to its shape
suspend fun customerReport(): List<ReportRow> {
    val orders = db.query("SELECT customer_id, total_minor, created_at FROM orders")  // 180k rows

    // group by customer
    val byCustomer = HashMap<Long, MutableList<OrderRow>>()
    for (o in orders) byCustomer.getOrPut(o.customerId) { mutableListOf() }.add(o)

    // latest order per customer
    val latest = HashMap<Long, OrderRow>()
    for ((id, list) in byCustomer) {
        var newest = list.first()
        for (o in list) if (o.createdAt > newest.createdAt) newest = o
        latest[id] = newest
    }

    // lifetime value per customer
    val lifetime = byCustomer.mapValues { (_, list) -> list.sumOf { it.totalMinor } }

    // rank by lifetime value, handling ties
    val ranked = lifetime.entries.sortedByDescending { it.value }
    val ranks = HashMap<Long, Int>()
    var rank = 0; var prevValue = Long.MIN_VALUE; var seen = 0
    for ((id, value) in ranked) { seen++; if (value != prevValue) { rank = seen; prevValue = value }; ranks[id] = rank }

    // running total
    var running = 0L
    return ranked.map { (id, value) ->
        running += value
        ReportRow(id, latest.getValue(id), value, ranks.getValue(id), running)
    }
    // + 300 more lines: pagination, a second variant grouped by month,
    //   null handling for customers with no orders, and tests for all of it
}

Every piece of that is ordinary code. None of it is wrong.

The symptom#

Slow, and memory-hungry:

customerReport()
  duration:    14.2s
  peak heap:   612 MB
  rows read:   180,412
  rows returned: 2,840

One hundred and eighty thousand rows transferred to produce 2,840.

It did not paginate. The rank and running total depend on the whole ordered set, so page 2 required recomputing everything — which is why the endpoint was 14 seconds on every request rather than once.

The tie-handling was subtly wrong, and I found it four months later:

three customers with lifetime value 50_000
  my ranks:     1, 1, 1   then the next customer at 4     ← intended
  actual:       1, 1, 1   then the next customer at 2     ← bug in the `seen` reset

Twenty lines of hand-rolled ranking with an off-by-one that no test caught because every test fixture had distinct values.

And then I wrote it twice more. A monthly-cohort variant (another 160 lines) and a product-category variant (another 140), each a copy of the same grouping machinery.

Why the obvious fix failed#

"Optimise the Kotlin." I did — swapped HashMap for primitive-keyed maps, avoided intermediate lists, streamed the result. 14.2s to 8.9s and 612MB to 410MB. A 37% improvement on something that should not have existed.

"Add an index." The query was SELECT * FROM orders with no predicate. There is nothing to index; the problem was that I was reading everything.

"Cache the report." Correct for a daily report, and it was a live screen with filters, so the cache key included the filters and the hit rate was 11%.

"Push the aggregation into the ORM." The ORM's aggregation API covered the group-by and the sum, and had no vocabulary for rank-with-ties or a running total — which is precisely the part I had hand-rolled, so I stopped there and kept my code.

That last one is the honest centre of this post: I hit the limit of the tool I knew, and instead of learning the tool that handles it, I wrote around it.

The actual mechanism#

Two costs, and one of them is invisible at the moment of the decision:

The cost of learning is bounded, paid once, and compounds — window functions appear in every reporting task afterwards.

The cost of avoiding is unbounded and paid repeatedly: the code to write, the tests, the review, the bug four months later, and the two copies for the variants.

learning window functions:   ~2 hours, once
avoiding them:               400 + 160 + 140 lines
                           + 14s per request
                           + 1 ranking bug
                           + 3 test suites
                           + every future reporting task

And the reason the trade feels rational in the moment is that the learning cost is visible and the avoidance cost is deferred. Two hours of reading documentation is a concrete thing I would have to stop and do today. Four hundred lines of code I know how to write feels like progress.

The specific technical point is worth stating too, because it is the thing I did not know: a window function computes a value per row, over a window of related rows, without collapsing them the way GROUP BY does. That is exactly the shape of "rank within the whole set" and "running total up to this row" — operations that are awkward in application code precisely because they need the whole ordered set at once, which the database already has.

The learning cost is bounded and visible. The avoidance cost is unbounded and deferred. That asymmetry is why "I already know how to do this the long way" wins in the moment and loses over a quarter.

The fix#

Eleven lines of SQL.

SELECT
    o.customer_id,
    -- latest order per customer, without a self-join
    FIRST_VALUE(o.id)         OVER w_recent  AS latest_order_id,
    FIRST_VALUE(o.created_at) OVER w_recent  AS latest_order_at,
    -- lifetime value, per row, WITHOUT collapsing the rows
    SUM(o.total_minor)        OVER (PARTITION BY o.customer_id) AS lifetime_minor,
    -- rank over the whole set, with correct tie handling
    DENSE_RANK()              OVER (ORDER BY SUM(o.total_minor)
                                             OVER (PARTITION BY o.customer_id) DESC) AS rank,
    -- running total down the ranked list
    SUM(SUM(o.total_minor) OVER (PARTITION BY o.customer_id))
                              OVER (ORDER BY …) AS running_minor
FROM orders o
WINDOW w_recent AS (PARTITION BY o.customer_id ORDER BY o.created_at DESC);

In practice the readable version uses a CTE, which is how it shipped:

WITH per_customer AS (
    SELECT customer_id,
           SUM(total_minor)                                      AS lifetime_minor,
           MAX(created_at)                                       AS latest_order_at
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    latest_order_at,
    lifetime_minor,
    DENSE_RANK() OVER (ORDER BY lifetime_minor DESC)              AS rank,
    SUM(lifetime_minor) OVER (ORDER BY lifetime_minor DESC
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_minor
FROM per_customer
ORDER BY rank
LIMIT :limit OFFSET :offset;

Four things that are strictly better, not just shorter:

DENSE_RANK() handles ties correctly by definition, which is the bug I shipped. RANK(), DENSE_RANK() and ROW_NUMBER() differ precisely in tie behaviour, and knowing which one you want is the kind of thing that takes five minutes to learn and cannot be hand-rolled reliably.

The ROWS BETWEEN frame is explicit. The default frame for an ordered window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats tied values as one group — a genuine difference for a running total, and the sort of default worth stating rather than inheriting.

It paginates. LIMIT/OFFSET works because the rank and running total are computed by the database over the full set regardless of which page is returned.

And the two variants are two WHERE clauses, not 300 lines:

-- monthly cohort: add one GROUP BY column
GROUP BY customer_id, date_trunc('month', created_at)

How we proved it#

The measurement:

                    hand-rolled   window functions
duration                 14.2s           0.23s
peak heap                612 MB           18 MB
rows transferred       180,412           2,840
lines of Kotlin            400               22   (mapping the result)
paginates?                  no              yes

Sixty-two times faster, and the heap number is the one that mattered operationally — 612MB per concurrent request was why the endpoint had a hard concurrency limit of three.

That the tie bug is gone, with the test I should have written first:

@Test
fun tiedLifetimeValuesShareARankAndTheNextRankSkips() = runTest {
    seedCustomersWithLifetimeValues(50_000, 50_000, 50_000, 30_000)
    val rows = report(limit = 10)
    assertEquals(listOf(1, 1, 1, 4), rows.map { it.rank })      // DENSE_RANK gives 1,1,1,2
}

Which failed, and taught me something: DENSE_RANK() gives 1,1,1,2 (no gaps) and RANK() gives 1,1,1,4 (gaps). The business wanted gaps, so the correct function is RANK() — and discovering that by writing the test is the version of this where I actually learned the tool rather than swapping one guess for another.

RANK():        1, 1, 1, 4   ✓ matches the requirement
DENSE_RANK():  1, 1, 1, 2
my hand-rolled: 1, 1, 1, 2  ← accidentally DENSE_RANK, and not what was asked for

And the two variants, re-implemented:

                      before        after
monthly cohort     160 lines     1 extra GROUP BY column
category variant   140 lines     1 extra PARTITION BY column

The learning cost, honestly tracked:

reading the Postgres window-function docs:      40 min
working through the frame-clause semantics:     35 min
getting RANK vs DENSE_RANK right:               15 min
rewriting the report:                           50 min
total                                        ~2h 20m

Two hours and twenty minutes against 700 lines across three implementations, a 14-second endpoint, and a ranking bug that reached production.

One limit worth stating: window functions are not portable across every database in identical form, and the WINDOW clause and some frame options vary. We are on Postgres and committed to it; a project genuinely needing database portability has a real reason to keep some logic in the application, and that reason is not "I have not learned it".

What this generalizes to#

Count the cost of not learning. It is usually paid in code you then own: the implementation, its tests, its review, its bugs, and every variant. The learning is bounded; the avoidance is not.

"I already know how to do this the long way" is the warning sign. Competence in a general tool makes it easy to route around a specialised one. Noticing the moment you reach for 400 lines of general-purpose code to do something a tool does in eleven is the skill.

Hand-rolling a well-specified operation gets the edge cases wrong. Ranking with ties, running totals across tied values, date bucketing, pagination stability. These have precise definitions and named implementations, and my version silently implemented a different definition than the one asked for.

Move computation to where the data already is. The database had all 180,000 rows in order already. Transferring them to compute something it could compute in place is the shape of this mistake, and it recurs with ORMs, with data frames, and with any API that makes fetching cheap and aggregating hard.

Related#

• The N+1 query that looked fast in development — another "move it to the database" case
• The index I added made the query slower, and the planner was right — reading the plan
• Offset pagination loses rows while you read — the pagination this enabled

Keep reading#

This is part of Developer productivity, honestly — what actually makes the work go faster, measured rather than assumed. Follow MobileByteSensei for the rest.