The Heavy Lifters

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

Burtch Works

Problem

Return the product categories where orders of $1,000 or more bring in more than half of the category's total revenue, listed alphabetically. We count an order's revenue as its total_amount in transactions and take its category from the ordered product in products.

In some categories, a handful of big orders carry the rest.

Tables

transactions

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

products

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

Expected output

Worked solution and explanation

What this problem is really testing

Remove the merchandising story and this is a per-group share test. Inside each category, does the revenue from orders of $1,000 or more beat half of everything the category sold? The skill is computing a conditional numerator and the full denominator over the same group in one pass. Finding the four-figure orders is the easy part. The trap is finding them with a WHERE before the GROUP BY. That removes the smaller orders, so every group's share becomes 100 percent, and on this data you return ten categories when only three qualify.

Build it up

Step 1: Attach a category to each order

Inner-join transactions to products on product_id. Some transactions carry a NULL product_id. They have no category, and the inner join is what keeps them out of the answer.

Step 2: Group by category

GROUP BY p.category gives one row per category. Both sums must be computed over that same set of rows, and that set includes the refund and the voided sale.

Step 3: Compare big-order revenue to half the total

In HAVING, require the conditional sum to be strictly greater than 0.5 * SUM(t.total_amount). Use total_amount as stored, because it is already the order's full value; never multiply it by quantity.

The solution

SELECT p.category
FROM transactions t
JOIN products p ON t.product_id = p.product_id
GROUP BY p.category
HAVING SUM(CASE WHEN t.total_amount >= 1000 THEN t.total_amount ELSE 0 END) > 0.5 * SUM(t.total_amount)
ORDER BY p.category

SELECT p.category
FROM transactions t
JOIN products p ON t.product_id = p.product_id
WHERE t.total_amount >= 1000
GROUP BY p.category
ORDER BY p.category

Filter first, then group

WHERE t.total_amount >= 1000 throws out the smaller orders before grouping, so each group holds only big-order revenue. Every category except 'Collectibles' has at least one such order, so ten categories come back.

Conditional sum over the whole group

The CASE inside SUM counts the small orders as zero without removing them, so SUM(t.total_amount) still sees the whole category. Only 'Beauty', 'Sports' and 'Toys' clear half.

Common follow-up questions

• How would you also report each category's big-order share as a percentage? (Moves both sums into the `SELECT` list next to a computed ratio.)
• What if the cutoff should be the top 10 percent of orders by `total_amount` instead of a fixed $1,000? (Tests deriving the threshold from the data, for example with a percentile in a CTE.)
• How does your query behave if `total_amount` can be `NULL`? (Tests how `CASE` and `SUM` treat missing values in the numerator and the denominator.)