Apple-style Company ChallengeMediumVerified answerSQLite live

Product Category Revenue Analysis

For each product category, what is the total revenue, number of orders, and average order value? Only show categories generating at least $200 in revenue.

  • Joins
  • Subqueries
  • Aggregation
  • HAVING
  • Numeric functions

Challenge brief

Understand the request

Finance Team is building the quarterly P&L report and needs revenue and transaction metrics broken down by product line.

Calculate total revenue, order count, and average order value per product category using a 3-table JOIN.

Return

  • category
  • total_revenue (rounded 2)
  • order_count
  • avg_order_value (rounded 2)

Constraints

  • Attribute each item’s price times quantity to its product category
  • Count each category/order pair once
  • Average category-attributed order revenue rather than the full order total
  • Return categories with at least $200 attributed revenue

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

products

  • product_idINTEGER
  • categoryVARCHAR(50)

order_items

  • order_idINTEGER
  • product_idINTEGER
  • quantityINTEGER
  • priceREAL

orders

  • order_idINTEGER
  • total_amountREAL
  • statusVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Product category is in products. Units sold and prices are in order_items. Order totals are in orders. Chain: products JOIN order_items on product_id, then JOIN orders on order_id.

Hint 2

INNER JOIN products to order_items on product_id. INNER JOIN orders on order_id. GROUP BY category. SUM(oi.price * oi.quantity) for revenue, COUNT(DISTINCT o.order_id) for order count, AVG(o.total_amount) for avg value. HAVING SUM >= 200.

Hint 3

Build question 7 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH category_orders AS (SELECT p.category, oi.order_id, SUM(oi.price * oi.quantity) AS category_order_revenue FROM products p INNER JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.category, oi.order_id) SELECT category, ROUND(SUM(category_order_revenue), 2) AS total_revenue, COUNT(*) AS order_count, ROUND(AVG(category_order_revenue), 2) AS avg_order_value FROM category_orders GROUP BY category HAVING SUM(category_order_revenue) >= 200 ORDER BY total_revenue DESC, category;

Why this works

First aggregate item revenue at category/order grain, then aggregate those rows by category. This keeps order counts distinct by construction and makes the average represent category-attributed order value rather than unrelated whole-order totals.

Success check

5 qualifying categories; Mac leads with $7,596 revenue and Audio averages $249 of category-attributed revenue per order.

Expected result

Use this output to verify values, aliases, ordering, and row count.

categorytotal_revenueorder_countavg_order_value
Mac759641899
iPhone25973865.67
iPad109911099
Audio4982249
Watch3991399

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.