Microsoft-style Company ChallengeHardVerified answerSQLite live

User Engagement Score

What is the total usage time in minutes for each user across all their product sessions?

  • Joins
  • Aggregation
  • NULL handling
  • Sorting

Challenge brief

Understand the request

Customer Success is building a product health score for each customer and needs total usage time as the primary engagement signal.

Sum usage_minutes per user_id from usage_logs.

Return

  • user_id
  • total_usage

Constraints

  • Include registered users with no usage and report zero minutes for them
  • Order by user ID

Data you will use

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

users

  • user_idINTEGER
  • countryTEXT

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

Start from users so every registered user remains in the result. LEFT JOIN usage_logs on user_id, then aggregate each user's usage.

Hint 2

Use COALESCE(SUM(ul.usage_minutes), 0) AS total_usage so a user with no matching usage rows reports zero.

Hint 3

Build question 19 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 u.user_id, COALESCE(SUM(ul.usage_minutes), 0) AS total_usage FROM users u LEFT JOIN usage_logs ul ON u.user_id = ul.user_id GROUP BY u.user_id ORDER BY u.user_id;

Why this works

Start from registered users, preserve users without usage, and convert only their missing aggregate to zero minutes.

Success check

13 users — user 8 leads with 580 minutes; user 13 has no usage and reports 0 minutes

Expected result

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

user_idtotal_usage
1210
260
3200
4150
5180
6110
7210
8580
9100
1095

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

SQL Interview Practice →

Return to the complete interview preparation experience.