Consistent High-Quantity Revenue

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

Problem

A single sale of fewer than two units disqualifies a customer's whole history with a product, so only pairs where every row in the transactions table moved two or more units count. For the merchandising team's review, total each qualifying customer and product pair's revenue, reading down customer by customer from the lowest id with each customer's biggest earners first.

Big orders, consistent revenue. A rare combination.

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 universal quantifier dressed up as a merchandising report: keep a customer and product pair only if every one of its sales moved two or more units. Anyone can total total_amount per pair. What separates candidates is where the quantity test lives. Put quantity >= 2 in WHERE and the engine throws away the short sales before the pair is ever formed, so a pair with one single-unit sale survives on its remaining rows and reports a total that is not even its real total. The rule is about the whole pair, so the test has to run after the rows are gathered.

Getting there

Step 1: Form the pair before judging it

GROUP BY user_id, product_id collects every sale of a pair, including the ones that will disqualify it. Nothing is filtered yet: a pair has to keep its bad rows to know it is bad.

Step 2: Turn 'every sale' into one number

'Every quantity is at least 2' says exactly what 'the smallest quantity is at least 2' says. MIN(quantity) collapses the pair into that one number, and HAVING MIN(quantity) >= 2 drops the pair when any sale falls short, the voided zero-unit row included.

Step 3: Sum every row of a survivor

SUM(total_amount) runs over the same group, so a surviving pair reports all of its revenue, refunds included. total_amount already holds each sale's revenue; nothing is recomputed from quantity.

Step 4: Lay it out customer by customer

ORDER BY user_id ASC, total_revenue DESC sorts on the alias the SELECT computed, so the reader scans one customer at a time with that customer's biggest pair on top.

SELECT
    user_id,
    product_id,
    SUM(total_amount) AS total_revenue
FROM transactions
GROUP BY user_id, product_id
HAVING MIN(quantity) >= 2
ORDER BY user_id ASC, total_revenue DESC

Row filter

WHERE quantity >= 2 runs before grouping. A pair with sales of 3, 1 and 4 units loses the 1-unit row and comes back with the revenue of the other two: a pair the rule excludes, reported with a shrunken total.

Group gate

HAVING MIN(quantity) >= 2 runs after grouping. The same pair has a minimum of 1 and disappears entirely; every pair that survives is summed over all of its rows.

Common follow-up questions

• How would you write the gate with `NOT EXISTS` instead? (A correlated probe for a sale of the same `user_id` and `product_id` with `quantity < 2`: phrasing 'for all' as 'there is no counterexample'.)
• What if the bar were 'at least 80% of sales move two or more units'? (`AVG(CASE WHEN quantity >= 2 THEN 1.0 ELSE 0 END) >= 0.8` in the group gate: the quantifier softens from all to most.)
• How would you list the sales that disqualified each excluded pair? (Keep the rows instead of collapsing them: carry each pair's `MIN(quantity)` alongside its rows and keep the short sales of pairs whose minimum is below 2.)
• Should a voided zero-unit sale disqualify a pair, or be ignored? (One sale has `quantity` 0 and `total_amount` 0.0; raising the business question beats silently filtering it.)