Microsoft-style Company ChallengeHardVerified answerSQLite live

Above Average Users

Which users have a total usage time above the average total usage across all users?

  • Joins
  • Subqueries
  • Aggregation
  • HAVING
  • NULL handling

Challenge brief

Understand the request

Customer Success is segmenting users for a premium engagement programme and needs those whose usage significantly exceeds the platform average.

Find users whose SUM(usage_minutes) exceeds the average-of-sums using a nested subquery in HAVING.

Return

  • user_id

Constraints

  • Include zero-usage registered users when calculating the platform-wide average
  • Return users whose total usage is strictly above that average
  • 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

You need two aggregations: first SUM minutes per user, then AVG of those sums. The inner subquery computes per-user totals; the outer AVG of those gives the platform average. HAVING compares each user's sum against that average.

Hint 2

HAVING SUM(usage_minutes) > (SELECT AVG(total_usage) FROM (SELECT SUM(usage_minutes) AS total_usage FROM usage_logs GROUP BY user_id)).

Hint 3

Build question 20 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
WITH user_totals AS (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), platform_average AS (SELECT AVG(total_usage) AS avg_usage FROM user_totals) SELECT ut.user_id FROM user_totals ut CROSS JOIN platform_average pa WHERE ut.total_usage > pa.avg_usage ORDER BY ut.user_id;

Why this works

Build one total for every registered user, including zero, then calculate the average from that complete population before filtering above-average users.

Success check

2 users — user 8 (580 min) and user 12 (500 min) are above the average of ~227 min

Expected result

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

user_id
1
3
7
8
12

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.