Keep Most Recent Record

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

Capital One

Problem

Our users table records each account's status and signup date, and we only want one representative per status. For each account status, return the single most recently signed-up user with their id, username, email, and signup date, keeping the larger user id if two accounts tie on the latest date.

Carbon copies clutter the table. Only the latest matters.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a top-one-per-group problem in disguise. For each value of account_status you need the whole record of the newest signup, not just the newest date. The tempting shortcut, GROUP BY account_status with MAX(signup_date), hands you the right date but throws away which user owned it, and any username or email you drag along comes from an arbitrary row in the group. Ship that and you pin one status's newest signup date onto some other user's identity.

Why MAX is not enough

GROUP BY + MAX

SELECT account_status, MAX(signup_date), username, email ... GROUP BY account_status gives the latest date per status, but username and email are ungrouped. A strict engine rejects the query; a lenient one fills them from a random row in the group, so the identity no longer matches the date.

Window dedup

ROW_NUMBER() ranks every row inside its account_status partition by recency, so filtering to rn = 1 keeps the entire newest row intact: identity and signup_date come from the same record.

Step 1: Partition by account_status

Each status is its own contest. PARTITION BY account_status restarts the numbering for every status, so you get one winner per group instead of one winner overall.

Step 2: Order newest first, with a tiebreaker

ORDER BY signup_date DESC puts the most recent signup at rank 1. Two accounts in a status can share a date, and ROW_NUMBER would then pick one at random, so add user_id DESC to make the winner deterministic across reruns.

Step 3: Keep rn = 1

ROW_NUMBER labels exactly one row per status with 1. Filter to rn = 1 in an outer query. You cannot put that test in the same SELECT as the window function, because the window is not computed until after the WHERE runs, which is why the ranking lives in a CTE.

The solution

WITH ranked AS (
  SELECT
    account_status,
    user_id,
    username,
    email,
    signup_date,
    ROW_NUMBER() OVER (
      PARTITION BY account_status
      ORDER BY signup_date DESC, user_id DESC
    ) AS rn
  FROM users
)
SELECT account_status, user_id, username, email, signup_date
FROM ranked
WHERE rn = 1;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Two accounts in the same status share the latest `signup_date`. Which one survives? (`ROW_NUMBER` breaks the tie by whatever you place after `signup_date DESC`. Name it explicitly, here `user_id DESC`, or the surviving row drifts between runs.)
• What if you needed the newest user per age bracket instead? (Swap the partition key to `age_bucket`. A `NULL` bucket forms its own partition, so decide whether an unknown bracket should appear in the output.)
• Could `QUALIFY` shorten this? (On Snowflake, BigQuery or DuckDB: `SELECT ... FROM users QUALIFY ROW_NUMBER() OVER (PARTITION BY account_status ORDER BY signup_date DESC, user_id DESC) = 1`. Same plan, no CTE.)