Microsoft-style Company ChallengeBeginnerVerified answerSQLite live

Users by Country

For regional product planning, how many registered users are represented in each country?

  • Aggregation
  • Sorting

Challenge brief

Understand the request

International Sales needs to understand the geographic distribution of the user base to prioritise regional sales investments.

Count users per country from the users table.

Return

  • country
  • user_count

Constraints

  • Return every represented country
  • Order countries alphabetically

Data you will use

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

users

  • user_idINTEGER
  • countryTEXT
  • signup_dateDATE

Hints, when you need them

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

Hint 1

All data is in users. GROUP BY country and COUNT rows per group. No JOIN needed.

Hint 2

SELECT country, COUNT(*) AS user_count FROM users GROUP BY country.

Hint 3

Build question 2 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 country, COUNT(*) AS user_count FROM users GROUP BY country ORDER BY country;

Why this works

GROUP BY country creates one group per country. COUNT(*) counts users in each group. US has 5 users (users 1, 3, 6, 8, 11). The 6 countries represent 12 total users.

Success check

6 countries — US leads with 5; CA and IN each have 2; DE, FR, UK each have 1

Expected result

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

countryuser_count
CA2
DE1
FR1
IN2
UK1
US6

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.