Malek Damiri

Keep the latest row per key with QUALIFY

·

History tables are everywhere in revenue data: plan changes, price changes, owner changes. The question is almost always "what is true now?", and the tempting answer, max() of each column, is wrong in a way that's easy to miss.

The data

create table plan_changes as select * from (values
  ('acme',    timestamp '2026-07-01 09:00', 'starter',    500),
  ('acme',    timestamp '2026-08-15 14:30', 'growth',     1200),
  ('acme',    timestamp '2026-08-15 14:30', 'growth',     1200),  -- loaded twice
  ('globex',  timestamp '2026-06-10 11:00', 'growth',     1200),
  ('initech', timestamp '2026-05-02 08:00', 'starter',    500),
  ('initech', timestamp '2026-09-01 10:00', 'enterprise', 4000)
) t(account, changed_at, plan, mrr);

What max() does

select account, max(plan) as plan, max(mrr) as mrr
from plan_changes
group by account
order by account;
account   plan      mrr
acme      starter   1200
globex    growth    1200
initech   starter   4000

Each column is maximized on its own, so the plan comes from one row and the MRR from another. max(plan) is alphabetical, which is how initech ends up on "starter" at enterprise pricing. Summing the raw table is worse: 8,600 of MRR, counting acme's old plan and its duplicate.

QUALIFY keeps whole rows

select account, plan, mrr
from plan_changes
qualify row_number() over (partition by account order by changed_at desc) = 1
order by account;
account   plan         mrr
acme      growth       1200
globex    growth       1200
initech   enterprise   4000

One row per account, taken whole, and current MRR is 6,400. qualify filters on a window function the way having filters on an aggregate, so there's no subquery.

Break ties on purpose

Acme has two rows at the same timestamp. Here they're identical, so it doesn't matter which one wins. When tied rows differ, row_number() picks one arbitrarily and the answer can change between runs. Add a second sort key that's always unique, such as a load timestamp or a row id.

qualify works in Snowflake, BigQuery, Databricks and DuckDB. In Postgres, wrap the window function in a subquery or use distinct on.

Every query here ran in DuckDB 1.5.1 and returned the output shown. This note was written with AI assistance.