The Final Sale

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

Databricks

Problem

An analyst reviewing the catalog dashboard found sale figures that matched no single sale, when only each product's most recent transaction in transactions belongs on the page and products that never sold have nothing to show. List those products alphabetically by name from products, with each one's category and the amount and date of its latest sale.

Every product has a last sale. When was it?

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

transactions

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

Expected output

Worked solution and explanation

What this really is

This is greatest-one-per-group dressed up as a dashboard fix. The skill is keeping the amount and the date from the same physical row. The tempting GROUP BY product_id with MAX(transaction_date) and MAX(total_amount) computes each column on its own, so it can stamp a product's biggest-ever sale with its newest date: two transactions fused into one that never happened. Joining back on the max date is the other reflex, and it emits a product twice whenever two sales share that date.

Break down the requirements

Step 1: Rank transactions inside each product

Partition transactions by product_id and order newest-first on transaction_date. Break date ties on transaction_id descending, because IDs climb with each sale, so the pick stays deterministic when two sales land on the same day.

Step 2: Keep only the latest row

Filter to rn = 1. That leaves exactly one row per product, and its total_amount and transaction_date belong to the same sale.

Step 3: Attach product metadata with an inner join

Join the latest rows to products on product_id to pick up product_name and category. The inner join is what keeps never-sold products off the page. The null-product transactions also fall away here, because they match no product.

Step 4: Sort alphabetically by name

Order by product_name alone. Names are unique in products, so the alphabetical order is already settled and a second key would decide nothing.

The solution

WITH ranked AS (
    SELECT product_id,
           total_amount,
           transaction_date,
           ROW_NUMBER() OVER (
               PARTITION BY product_id
               ORDER BY transaction_date DESC, transaction_id DESC
           ) AS rn
    FROM transactions
)
SELECT p.product_name,
       p.category,
       r.total_amount     AS latest_sale_amount,
       r.transaction_date AS last_sale_date
FROM products p
JOIN ranked r ON r.product_id = p.product_id
WHERE r.rn = 1
ORDER BY p.product_name

Common follow-up questions

• What if the dashboard also wanted the two most recent sales per product? (Change the filter to `rn <= 2`. The rest of the query is identical.)
• Why break ties on `transaction_id` and not leave them to the engine? (Without it, two sales on the same `transaction_date` can swap between runs and the amount shown flickers.)
• How would you include products that have never sold, with NULL sale columns? (Use `products LEFT JOIN ranked` with the `rn = 1` condition moved into the join's ON clause.)
• How would the plan change at 1 billion transactions and 1 million products? (Partition `transactions` by date and maintain a per-product latest-sale table that is updated incrementally.)