Keep the Latest Row per Customer in DuckDB

Ben used to be on Pro. He downgraded this morning. A report that finds his latest Pro record will still call him a Pro customer. A report that finds his latest record first will get the current state right.

We will solve both parts of the problem: choose one complete row per customer, and apply a current-state filter at the correct stage.

The update history

These are complete customer snapshots, not partial patches. Every update contains the whole current plan. id is a unique ingestion sequence number; a larger ID wins when two updates have the same timestamp. That is our explicit tie-break policy, not a claim that every source system should use ingestion order.

Ada has two records at 10:00. Ben has a later downgrade. Cy has only a record with an unknown timestamp. These cases make an apparently simple “latest” query worth examining.

Rank the complete records

Partition the rows by customer and order each partition from newest to oldest:

SELECT id, customer_id, plan,
       row_number() OVER (PARTITION BY customer_id
         ORDER BY updated_at DESC NULLS LAST, id DESC) AS recency_rank
FROM updates ORDER BY customer_id, recency_rank;

ROW_NUMBER assigns a unique rank within each customer. updated_at DESC puts newer events first. NULLS LAST makes the policy for unknown times visible. id DESC resolves Ada’s timestamp tie reproducibly.

Cy still receives rank 1 because there is no competing record. If your business rule excludes undated updates entirely, apply that as a separate documented rule. “Unknown timestamp” is not the same as “oldest known event.”

A query using only MAX(updated_at) gives you a timestamp, not necessarily the rest of the matching record. Joining that maximum back to the history can return both of Ada’s tied updates. Selecting unrelated maxima for the plan and name can even assemble a row that never existed.

Keep rank one with QUALIFY

DuckDB’s QUALIFY clause filters the results of window functions. It lets us keep the winning row without exposing the rank as a final column:

SELECT id, customer_id, name, plan
FROM updates
QUALIFY row_number() OVER (
  PARTITION BY customer_id
  ORDER BY updated_at DESC NULLS LAST, id DESC
) = 1
ORDER BY customer_id;

The result keeps Ada’s Pro record, Ben’s Free record, and Cy’s only record. We have preserved entire records under a declared ordering rule.

This ordering must be total for the result to be deterministic. If your final tie-break column also has duplicates, either add a stable unique key or return ties for review. Do not let a convenient row order become an accidental business decision.

Filter current state after selecting it

WHERE plan = 'pro' removes rows before the window function runs. That would hide Ben’s downgrade and allow his earlier Pro record to win. Put the latest-record query in a CTE, then filter its output.

WITH latest AS (
  SELECT customer_id, name, plan FROM updates
  QUALIFY row_number() OVER (PARTITION BY customer_id
    ORDER BY updated_at DESC NULLS LAST, id DESC) = 1
)
SELECT customer_id, name FROM latest
WHERE plan = 'pro' ORDER BY customer_id;

The distinction generalizes: latest successful payment and latest payment if successful answer different questions. Choose the sequence that matches the wording of the report.

Know what “latest” means in your system

An event timestamp and an ingestion timestamp describe different things. A late-arriving correction can be newest by ingestion time but older by event time. Establish which one governs the report before choosing an ORDER BY.

This example also assumes full snapshots. For patch events that omit unchanged fields, choosing one row is not enough; you must reconstruct state or use a source that already provides complete versions.

Once the latest-record query is reliable, save it as a view and test the tie and downgrade cases whenever its rules change. You can then compare snapshots to audit which customers’ current states changed between runs.