Find the top 3 products by total revenue. Revenue is calculated as price * quantity sold. Return product name and total revenue, ordered by revenue descending.
This basic aggregation problem is fundamental to Shopify's merchant analytics. Every e-commerce business needs to know which products drive the most revenue to inform inventory, marketing, and pricing decisions. This question tests your ability to perform calculations within aggregations, use GROUP BY correctly, and apply LIMIT for top-N queries—essential skills for any data analyst role.
Core concepts: SUM() with calculated columns (price * quantity), GROUP BY for product-level aggregation, ORDER BY for sorting, and LIMIT for top-N selection. Understanding the order of operations (calculation happens before aggregation) and how to combine multiple aggregate functions is key.
Shopify merchants use similar queries to: generate best-seller reports for inventory planning, identify products to feature in marketing campaigns, calculate product-level profitability, optimize warehouse space allocation, power recommendation engines with popularity signals, and create automated reorder triggers for high-velocity items.
When tackling this Shopify problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT
product_name,
SUM(price * quantity) AS total_revenue
FROM orders
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 3This is the canonical approach: multiply price and quantity per row, sum them per product, then rank and limit. GROUP BY collapses all rows for the same product_name into one aggregate row. It is concise, universally supported, and easy to read. The main risk is assuming price is fixed per product — if price varies by order (e.g., discounts), SUM(price * quantity) correctly handles each row independently. This is the expected answer for an Easy-difficulty interview question and demonstrates solid aggregation fundamentals.
WITH product_totals AS (
SELECT
product_name,
SUM(price * quantity) AS total_revenue
FROM orders
GROUP BY product_name
),
ranked AS (
SELECT
product_name,
total_revenue,
RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM product_totals
)
SELECT product_name, total_revenue
FROM ranked
WHERE revenue_rank <= 3
ORDER BY total_revenue DESCUsing RANK() instead of LIMIT handles ties correctly — if two products share the 3rd highest revenue, both are returned. LIMIT 3 would arbitrarily drop one. This approach is more complex but more defensible in production scenarios. The CTE makes each step explicit and testable. Prefer this when business requirements mandate that ties are always included. The downside is verbosity and a slight overhead from the additional window function pass, but correctness outweighs simplicity here.
For this query, the dominant cost is the full scan of the orders table to compute SUM(price * quantity) per product. An index on product_name can accelerate the GROUP BY, but since the aggregation requires visiting every row to compute revenue, a covering index on (product_name, price, quantity) provides the most benefit — the engine avoids fetching the base table rows entirely. At Shopify scale (billions of orders), pre-aggregating daily or hourly revenue into a summary table is common practice. LIMIT 3 is highly efficient because it allows the engine to stop after finding the top 3 during sort, rather than materializing all results. The RANK() variant adds a window sort step, which has O(n log n) cost; for very large product catalogs this is measurable but typically acceptable. Partitioning the orders table by created_at and filtering by a time window before aggregating is the most impactful optimization at scale.
The most common mistake is computing revenue as price + quantity (addition) rather than price * quantity (multiplication). Another pitfall is forgetting LIMIT or capping at the wrong number. Candidates sometimes GROUP BY product_id without including product_name in the SELECT, causing an error or ambiguous results. Using DISTINCT inside SUM is occasionally misapplied here — SUM(DISTINCT price * quantity) would deduplicate identical revenue rows, which is incorrect. NULL handling matters too: if price or quantity is NULL, the row contributes nothing to SUM, which may silently undercount revenue. Always check whether the problem implies a time filter that the candidate should infer.
LIMIT 3 will arbitrarily return only one of them, depending on sort stability. The correct approach is to use RANK() OVER (ORDER BY total_revenue DESC) and filter WHERE rank <= 3 — this returns all tied products at any rank position. DENSE_RANK() would also work and is more compact for consecutive rankings. This distinction is important in production reports where ties must be surfaced fairly.
Add a category column to the SELECT and GROUP BY, then use RANK() OVER (PARTITION BY category ORDER BY total_revenue DESC) in a CTE, filtering WHERE rank <= 3 in the outer query. LIMIT 3 alone cannot produce per-group tops — window functions with PARTITION BY are the correct tool for this pattern, which is a very common interview follow-up.
Always SUM(price * quantity). SUM(price) * SUM(quantity) is mathematically incorrect — it multiplies the total price across all orders by the total quantity across all orders, producing a vastly inflated number. The correct revenue for each order row is price × quantity; those must be summed independently. This is a classic aggregation trap that catches candidates who reason about the formula without considering row-level semantics.