Keeping One Row Per Key

Concepts covered: Window Deduplication, Row Numbering, Window Partitioning, Common Table Expressions

With the key agreed and the copies counted, the next mistake is a query that collapses the key alone. SELECT customer_id, order_date FROM orders GROUP BY customer_id, order_date returns the right 3 rows but has no order_id in them, and amount and created_at are gone as well. The interviewer asked for one row per key, and a row has 5 columns. The 3 columns that were never part of the key have to come from somewhere, and where they come from is the whole question.

Aggregating the other columns invents a row

The first instinct is to aggregate them. For this example, orders gains email as a sixth column, which the 10:00 write left NULL. Take the 4 rows dated 2024-01-01: customer 1's 3 writes, 100 at 09:00, 140 at 10:00 and 110 at 11:00, and customer 2's single write of 75 at 12:45. With one date in play, grouping by customer_id alone is the same as grouping by the agreed key. Wrap every non-key column in MAX and the query runs without complaint.

SELECT
  customer_id,
  MAX(created_at) AS last_created_at,
  MAX(amount) AS max_amount,
  MAX(email) AS email
FROM orders
GROUP BY customer_id

Customer 1 comes out at 11:00 with an amount of 140 and the email ana@shopmail.com. No source row carries that combination: the time belongs to the 11:00 write, the amount to the 10:00 write, and the email to whichever write had one, since MAX skips NULL and so hides that a write lost it. The latest write of this order says 110, and the revenue report built on this result will say 140. Aggregating column by column assembles a record from pieces of different rows, and the record it assembles never existed. This is what the question tests: whether you know that keeping one row per key means keeping a row, with its columns travelling together, rather than a key with the best value of each column stapled on.

Rank the copies, keep rank 1

WITH ranked AS (
  SELECT
    order_id,
    customer_id,
    order_date,
    amount,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id, order_date
      ORDER BY created_at DESC
    ) AS rn
  FROM orders
)
SELECT order_id, customer_id, order_date, amount, created_at
FROM ranked
WHERE rn = 1;

SELECT
  customer_id,
  order_date,
  created_at,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id, order_date
    ORDER BY created_at DESC
  ) AS rn
FROM orders

PARTITION BY customer_id, order_date is the key from the first section written into the query: rows that share both columns form one partition and are numbered apart from every other partition. ORDER BY created_at DESC decides the numbering inside each one, newest first, so the 11:00 write of (1, 2024-01-01) gets rn 1 and the 09:00 write gets 3, with the 10:00 write between them at 2. Rows 1004 and 1005 sit alone in their partitions and get 1. The outer WHERE rn = 1 keeps 1003 along with those 2 singletons: 3 rows, the count the previous section said to expect, and row 1003 arrives whole, amount 110 beside its 11:00 timestamp. Change the 2 clauses and the same shape answers any key and any survivor rule.

Why the filter sits in an outer query

WHERE is evaluated before window functions, so in the SELECT that defines rn the column does not exist yet, and WHERE rn = 1 fails with an unknown column error. The CTE computes rn first, and the outer query filters a result in which rn is an ordinary column. Say it as you type the WITH: 'I'm wrapping the ROW_NUMBER in a CTE because the outer WHERE can't filter on a window function in the same select.' Interviewers hear that sentence as evidence you have written this against a real engine, because the error it avoids is the one everybody hits the first time.

-- Snowflake, BigQuery, Databricks
SELECT order_id, customer_id, order_date, amount, created_at
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id, order_date
  ORDER BY created_at DESC
) = 1;

QUALIFY is to window functions what HAVING is to aggregates: a filter that runs after the function has been computed. Postgres offers DISTINCT ON (customer_id, order_date) with an ORDER BY customer_id, order_date, created_at DESC, which keeps the first row of each key in that order. Both are shortcuts for one dialect. The CTE form runs everywhere, so write it unless the interviewer has named the engine, and name the shortcut as you skip it: 'on Snowflake this is a QUALIFY, on Postgres a DISTINCT ON, and this form is portable.'

About this lesson

This section is part of the Deduplication: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.