Microsoft-style Company ChallengeMediumVerified answerSQLite live

Subscription and Usage Per User

For each user, show their subscribed product name, subscription price, and total usage minutes.

  • Joins
  • Aggregation
  • NULL handling
  • Sorting

Challenge brief

Understand the request

Customer Success is building a health-score view and needs to see each user's subscription product, price paid, and total usage side by side.

Join subscriptions, products, and usage_logs to produce a per-user product engagement summary.

Return

  • user_id
  • product_name
  • price
  • total_usage (NULL if no usage logged)

Constraints

  • Include every subscription record even when the matching user-product has no usage
  • Match usage on both user and product
  • Place null usage last, then resolve ties deterministically

Data you will use

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

subscriptions

  • user_idINTEGER
  • product_idINTEGER
  • priceINTEGER

products

  • product_idINTEGER
  • product_nameTEXT

usage_logs

  • user_idINTEGER
  • product_idINTEGER
  • usage_minutesINTEGER

Hints, when you need them

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

Hint 1

Three tables: subscriptions (for user+product+price), products (for name), usage_logs (for usage). LEFT JOIN usage_logs on BOTH user_id AND product_id — this matches usage for the exact product they're subscribed to, not any product they might have used.

Hint 2

INNER JOIN subscriptions to products on product_id. LEFT JOIN usage_logs on s.user_id = ul.user_id AND s.product_id = ul.product_id. GROUP BY user, product, price. SUM(ul.usage_minutes).

Hint 3

Build question 29 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 s.user_id, p.product_name, s.price, SUM(ul.usage_minutes) AS total_usage FROM subscriptions s INNER JOIN products p ON s.product_id = p.product_id LEFT JOIN usage_logs ul ON s.user_id = ul.user_id AND s.product_id = ul.product_id GROUP BY s.subscription_id, s.user_id, p.product_name, s.price ORDER BY total_usage IS NULL, total_usage DESC, s.user_id, p.product_name;

Why this works

Keep subscription grain explicit, match usage on both user and product, and retain the historical Azure subscription even though it has no matching usage. The null-placement expression makes ordering portable in Core SQL.

Success check

12 users — user 8 (Azure, $300, 580 min) leads; all users have usage in this dataset

Expected result

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

user_idproduct_namepricetotal_usage
8Azure300580
12Azure300500
1Microsoft Office120210
7Teams80210
3Azure300200
5Azure300180
4Teams80150
11Teams80130
6Microsoft Office120110
9Microsoft Office120100

Previewing 10 of 13 expected rows. Run the query in the editor to inspect the full result.

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.