Microsoft-style Company ChallengeHardVerified answerSQLite live

Top User per Country by Usage

In each country, which user has logged the most total product usage time?

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Aggregation

Challenge brief

Understand the request

Regional Sales is identifying the top-engaged customer in each country to invite to a regional customer advisory board.

Find the highest-usage user per country using a CTE and ROW_NUMBER() partitioned by country.

Return

  • country
  • user_id
  • total_usage

Constraints

  • Return one usage leader per represented country
  • When user totals tie, the lower user ID wins
  • Show the largest country-leading totals first

Data you will use

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

usage_logs

  • user_idINTEGER
  • usage_minutesINTEGER

users

  • user_idINTEGER
  • countryTEXT

Hints, when you need them

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

Hint 1

Two-step pattern: CTE aggregates total usage per (country, user) and assigns ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_usage DESC). Outer query: WHERE rn = 1 picks the top user in each country.

Hint 2

CTE: INNER JOIN usage_logs to users. GROUP BY user_id, country. SUM(usage_minutes) AS total_usage. ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(usage_minutes) DESC) AS rn. Outer: WHERE rn = 1.

Hint 3

Build question 30 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_usage AS (SELECT ul.user_id, u.country, SUM(ul.usage_minutes) AS total_usage, ROW_NUMBER() OVER (PARTITION BY u.country ORDER BY SUM(ul.usage_minutes) DESC, ul.user_id ASC) AS rn FROM usage_logs ul INNER JOIN users u ON ul.user_id = u.user_id GROUP BY ul.user_id, u.country) SELECT country, user_id, total_usage FROM user_usage WHERE rn = 1 ORDER BY total_usage DESC, country;

Why this works

ROW_NUMBER() resets to 1 for each country's top user. The US has 5 users but only user 8 (580 min) wins. CA has 2 users (12=500, 5=180) — user 12 wins. WHERE rn=1 extracts exactly one winner per country.

Success check

6 countries — US: user 8 (580 min), CA: user 12 (500 min), IN: user 7 (210 min), UK: user 4 (150 min), DE: user 9 (100 min), FR: user 10 (95 min)

Expected result

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

countryuser_idtotal_usage
US8580
CA12500
IN7210
UK4150
DE9100
FR1095

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.