Microsoft-style Company ChallengeHardVerified answerSQLite live

Top Revenue Product

Which product generates the highest total subscription revenue?

  • Aggregation
  • Sorting
  • Top-N

Challenge brief

Understand the request

Revenue Leadership needs to identify the single highest-revenue product to prioritise for the investor presentation on product mix.

Find the top revenue product using GROUP BY + ORDER BY + LIMIT 1 on subscriptions.

Return

  • product_id
  • revenue

Constraints

  • Return exactly one product
  • When revenue ties, the lower product ID wins

Data you will use

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

subscriptions

  • subscription_idINTEGER
  • user_idINTEGER
  • product_idINTEGER
  • start_dateDATE
  • end_dateDATE
  • priceINTEGER

Hints, when you need them

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

Hint 1

Revenue comes from SUM(price) per product in subscriptions. GROUP BY product_id, SUM price, ORDER BY desc, LIMIT 1 returns the single top product.

Hint 2

SELECT product_id, SUM(price) AS revenue FROM subscriptions GROUP BY product_id ORDER BY revenue DESC LIMIT 1.

Hint 3

Build question 18 from the required result grain: choose the driving table, add only the joins and filters needed for that grain, then apply aggregation and deterministic ordering.

Verified SQL answer

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

Reveal solution and explanation
SELECT product_id, SUM(price) AS revenue FROM subscriptions GROUP BY product_id ORDER BY revenue DESC, product_id ASC LIMIT 1;

Why this works

Azure (product_id=2) has 4 subscriptions × $300 = $1200, the highest. Office has 5 × $120 = $600. Teams has 3 × $80 = $240. LIMIT 1 picks only the top row.

Success check

1 row — product_id: 2 (Azure) with $1200 total revenue

Expected result

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

product_idrevenue
21500

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.