Meta-style Company ChallengeMediumVerified answerSQLite live

Top Poster Per Country

In each country, which user has created the most posts?

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Aggregation

Challenge brief

Understand the request

International Growth is identifying the most active content creator in each country to invite to a regional creator programme.

Find the highest-posting user per country using a CTE and ROW_NUMBER window function.

Return

  • country
  • username
  • post_count

Constraints

  • Return one winner per country that has at least one poster
  • When post counts tie, the lower user ID wins
  • Show the largest winning post counts first, then country name

Data you will use

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

users

  • user_idINTEGER
  • usernameVARCHAR(50)
  • countryVARCHAR(50)

posts

  • post_idINTEGER
  • user_idINTEGER

Hints, when you need them

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

Hint 1

This is a top-N-per-group problem where the group is country. Pattern: CTE to aggregate post counts per user including ROW_NUMBER() OVER (PARTITION BY country ORDER BY count DESC), then outer query WHERE rn = 1.

Hint 2

CTE: INNER JOIN users to posts, GROUP BY user, COUNT posts, ROW_NUMBER() OVER (PARTITION BY country ORDER BY COUNT DESC) AS rn. Outer: WHERE rn = 1.

Hint 3

Start with the tables that establish the result grain for question 20, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH post_counts AS (SELECT u.user_id, u.country, u.username, COUNT(p.post_id) AS post_count, ROW_NUMBER() OVER (PARTITION BY u.country ORDER BY COUNT(p.post_id) DESC, u.user_id ASC) AS rn FROM users u INNER JOIN posts p ON u.user_id = p.user_id GROUP BY u.user_id, u.country, u.username) SELECT country, username, post_count FROM post_counts WHERE rn = 1 ORDER BY post_count DESC, country ASC

Why this works

Count posts per user and country, choose one deterministic winner per country using user_id for ties, then order the country winners consistently.

Success check

3 countries — USA: john_doe (2 posts), Canada: sarah_jones (1), UK: mike_wilson (1)

Expected result

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

countryusernamepost_count
USAjohn_doe2
Canadasarah_jones1
UKmike_wilson1

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.