Top Revenue Products H1

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

General Assembly

Problem

Each row of transactions is one sale with its product and line total, and a sale with no product recorded still counts, pooled into one line of its own. Over January through June of every year on record, return the top 5 products by total revenue, highest first, and if products are tied at the cutoff, include all of them.

First half of the year. Which products led the revenue race?

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 top-N-with-ties question dressed up as a revenue report. The cutoff separates candidates: 'top 5' with an explicit tie clause is a standing, not a row count. ORDER BY total_revenue DESC LIMIT 5 reads right and keeps exactly five rows. So on the day two products land level at fifth, one of them vanishes, and which one is up to the engine. The window hides two more traps. January through June means those months in every year on record. The sales with no product_id are a real revenue line, and at 9740.94 it tops the list.

Step 1: Pool the first half of every year

Pull the month out of transaction_date and keep 1 through 6 without pinning a year: the table spans several years and each one contributes its January to June. strftime('%m', ...) returns text like '03', so the CAST(... AS INTEGER) is what makes BETWEEN 1 AND 6 numeric. Leave it off and text never sits between two integers, so every row is filtered out.

Step 2: Sum total_amount per product_id

total_amount is already the line total, so there is no quantity times price to compute. GROUP BY product_id collects every product-less sale into one NULL group. That is the line the statement keeps, and it is the largest. A WHERE product_id IS NOT NULL added out of habit silently promotes the sixth product into the list.

Step 3: Cut on the standing, not the row count

DENSE_RANK() OVER (ORDER BY SUM(total_amount) DESC) gives level totals the same standing, so rnk <= 5 keeps every product tied at fifth. The window is evaluated after GROUP BY, which is why it can order by the aggregate in the same SELECT. On this data nobody is level at fifth, so the list is five rows. The query stays correct the day a tie appears, and a LIMIT does not.

The solution

SELECT product_id, total_revenue
FROM (
  SELECT product_id,
         SUM(total_amount) AS total_revenue,
         DENSE_RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rnk
  FROM transactions
  WHERE CAST(strftime('%m', transaction_date) AS INTEGER) BETWEEN 1 AND 6
  GROUP BY product_id
) ranked
WHERE rnk <= 5
ORDER BY total_revenue DESC

A five-row cap

ORDER BY total_revenue DESC LIMIT 5 stops at five rows. When two products are level at fifth, one is dropped, and which one depends on the order the engine happened to emit.

A fifth-standing cutoff

DENSE_RANK() with rnk <= 5 stops at the fifth standing. Every product level at fifth stays, so the list can grow past five and is the same on every run.

Common follow-up questions

• How would you rewrite the month predicate so partition pruning stays on while still pooling every year? (Tests whether you know a function on the partition column defeats pruning.)
• If `total_amount` can be negative for refunds, does the list still reflect best sellers? (Gross versus net revenue: the table holds a refund and a voided sale.)
• How would you show each of these products' first-half revenue year over year? (Conditional aggregation by year, pivoting the same product set into columns.)