The Last Checkout

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

L4

Asked in

Walmart

Problem

Find the date of each user's most recent checkout in transactions and how many purchase rows the user logged on that day, oldest last checkout first. A shopper who bought several items that day has one row per item, and each of those rows counts.

Their last visit. Everything in the bag.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

This is a two-phase per-user problem dressed up as a retention report. One question picks the date: the latest transaction_date across all of a user's rows. The second question counts, and it counts over a different set: only the rows on that date. The reflex is MAX(transaction_date) and COUNT(*) in one GROUP BY user_id. The dates come out right, which is why the mistake survives review. But the count runs over the whole group, so every user reports their lifetime 12 to 14 rows instead of the 1 they bought on their last day.

SELECT MAX(transaction_date) AS transaction_date,
       user_id,
       COUNT(*) AS purchase_count
FROM transactions
GROUP BY user_id
ORDER BY MAX(transaction_date)

Narrow first, then count

Step 1: Mark each user's latest date

RANK() OVER (PARTITION BY user_id ORDER BY transaction_date DESC) gives every row on a user's newest date rank 1. The partition is what scopes 'latest' to the user; without it you get the latest date in the whole table.

Step 2: Keep only the rank-1 rows

WHERE date_rank = 1 is the narrowing the one-pass query cannot express. It throws away the rest of each user's history before anything is counted.

Step 3: Count what is left

GROUP BY transaction_date, user_id with COUNT(*) now counts over the right set. Each user has only one surviving date, so grouping by the date as well costs nothing, and the date comes back without being wrapped in an aggregate.

Step 4: Put the quietest users on top

ORDER BY transaction_date ascending puts the longest-lapsed buyers first, which is the end of the list a re-engagement campaign works from. Two users share the same last checkout date, and they may come back in either order.

The solution

WITH ranked AS (
    SELECT user_id,
           transaction_date,
           RANK() OVER (
               PARTITION BY user_id
               ORDER BY transaction_date DESC
           ) AS date_rank
    FROM transactions
)
SELECT transaction_date,
       user_id,
       COUNT(*) AS purchase_count
FROM ranked
WHERE date_rank = 1
GROUP BY transaction_date, user_id
ORDER BY transaction_date

Common follow-up questions

• How would you keep only users whose last checkout is more than 30 days before 2026-09-12? (Filter the final result on `transaction_date < '2026-08-13'`, because the latest date has to be found before it is filtered.)
• How would you also return what each user spent on that last date? (Carry `total_amount` through the CTE and add `SUM(total_amount)` beside `COUNT(*)`.)
• What if the count should be unique products rather than rows? (Use `COUNT(DISTINCT product_id)`, and decide what a row with a missing `product_id` means.)
• How would you solve this without a window function? (Join `transactions` to a per-user `MAX(transaction_date)` on both `user_id` and the date, then count. It is correct, but it reads the table twice.)