Microsoft-style Company ChallengeMediumVerified answerSQLite live

Most Used Product

Which product has the highest total usage time across all sessions?

  • Aggregation
  • Sorting
  • Top-N

Challenge brief

Understand the request

Product Strategy wants to identify which product gets the most actual usage time — the sticky product — to double down on its success factors.

Find the single product with the highest SUM(usage_minutes) using GROUP BY + ORDER BY + LIMIT 1.

Return

  • product_id
  • total_usage

Constraints

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

Data you will use

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

usage_logs

  • log_idINTEGER
  • user_idINTEGER
  • product_idINTEGER
  • usage_dateDATE
  • usage_minutesINTEGER

Hints, when you need them

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

Hint 1

All usage data is in usage_logs. GROUP BY product_id, SUM minutes, ORDER BY desc, LIMIT 1 to get the top product. No JOIN needed — product_id is sufficient.

Hint 2

SELECT product_id, SUM(usage_minutes) AS total_usage FROM usage_logs GROUP BY product_id ORDER BY total_usage DESC LIMIT 1.

Hint 3

Build question 13 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(usage_minutes) AS total_usage FROM usage_logs GROUP BY product_id ORDER BY total_usage DESC, product_id ASC LIMIT 1;

Why this works

Azure (product_id=2) totals 1460 minutes: 200+180+300+280+260+240 = 1460. Office totals 575 minutes. Teams totals 490 minutes. LIMIT 1 picks the single top row.

Success check

1 row — product_id: 2 (Azure) with 1460 total minutes

Expected result

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

product_idtotal_usage
21460

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.