Microsoft-style Company ChallengeMediumVerified answerSQLite live

Average Usage per Product

What is the average session length in minutes for each Microsoft product?

  • Joins
  • Aggregation
  • NULL handling
  • Sorting

Challenge brief

Understand the request

Product Usage Team is benchmarking session length per product to prioritise UX improvements in the products with the lowest engagement time.

Calculate average usage_minutes per product by joining usage_logs to products.

Return

  • product_name
  • avg_usage (raw average — no rounding required)

Constraints

  • Include every catalog product, even when it has no usage sessions
  • Leave average usage null when no sessions exist
  • Show non-null averages from highest to lowest

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

products

  • product_idINTEGER
  • product_nameTEXT
  • categoryTEXT

Hints, when you need them

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

Hint 1

Usage minutes are in usage_logs. Product names are in products. JOIN on product_id, GROUP BY product, AVG usage_minutes.

Hint 2

INNER JOIN usage_logs to products on product_id. GROUP BY p.product_name. AVG(u.usage_minutes) AS avg_usage.

Hint 3

Build question 12 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 p.product_name, AVG(u.usage_minutes) AS avg_usage FROM products p LEFT JOIN usage_logs u ON p.product_id = u.product_id GROUP BY p.product_id, p.product_name ORDER BY avg_usage IS NULL, avg_usage DESC, p.product_name;

Why this works

Preserve every product with an outer join. AVG naturally ignores null matches and returns null for Dynamics, which has no sessions; the ordering places that unknown average last.

Success check

3 products — Azure highest (243.3 min avg), Teams (122.5 min), Office (95.8 min)

Expected result

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

product_nameavg_usage
Azure243.33333333333334
Teams122.5
Microsoft Office95.83333333333333
DynamicsNULL

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.