Top Recent Sellers

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

Domain

SQL

Difficulty

easy

Seniority

L5

Asked in

Vanguard

Problem

Our marketplace homepage is getting a three-slot trending widget, filled from the transactions table. It should show the products with the highest total sales amount over the last 30 days, largest first.

Fresh data, top sellers. The recent leaderboard.

Tables

transactions

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

Expected output

Worked solution and explanation

This is a windowed revenue leaderboard in a homepage-widget costume. Everyone writes the GROUP BY product_id. What separates candidates is reading 'sales' as money and pinning 'last 30 days' to today. In this window nearly every product sold exactly once, so a COUNT(*) version ties them all at 1 and hands the widget an arbitrary three. Drop the cutoff and you sum every year of history: totals roughly double, and the top slot goes to product 5852, which has not sold in months.

Getting to the answer

Step 1: Pin the window to today

'Last 30 days' counts back from today: transaction_date >= DATE '2026-09-12' - INTERVAL '30 days'. CURRENT_DATE resolves to the same day here, so either spelling grades. transaction_date holds plain 'YYYY-MM-DD' strings, so it compares directly and there is no time of day to strip.

Step 2: Sum the money per product_id

SUM(total_amount) grouped by product_id. Refunds and voids sit in the table as negative and zero amounts, so they net into the total, which is what revenue should do.

Step 3: Sort, then cut to three

ORDER BY total_sales DESC and then LIMIT 3. The cut keeps whatever arrives first, so the sort has to happen before it. The third and fourth totals (1222.29 and 1208.82) do not tie, so the cut is clean.

SELECT product_id, SUM(total_amount) AS total_sales
FROM transactions
WHERE transaction_date >= DATE '2026-09-12' - INTERVAL '30 days'
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 3;

Counting sales

COUNT(*) per product_id: in this window each product sold once, so every product ties at 1 and LIMIT 3 keeps whichever three the engine meets first.

Summing them

SUM(total_amount) per product_id: the totals separate cleanly (1330.05, 1316.58, 1222.29 at the top), so money decides the three slots.

Common follow-up questions

• Two products tie for the third slot. Which one gets it? (Tests adding a deterministic tiebreaker such as `product_id` to the sort before the `LIMIT`.)
• Marketing now wants the top three per `category`. (Tests moving from one global cut to a cut scoped per group, joining `products`.)
• Should a product whose only activity in the window is a refund ever show up? (Tests whether netted negatives can surface, and whether to filter on `total_sales > 0`.)