Meta-style Company ChallengeEasyVerified answerSQLite live

Post Count Per User

How many posts has each user created, and who posts the most?

  • Joins
  • Aggregation
  • HAVING
  • Sorting

Challenge brief

Understand the request

Content Analytics wants to understand which users are the most prolific content creators on the platform.

Show post count per user — username, full name, and total post count for users with at least 1 post.

Return

  • username
  • full_name
  • post_count

Constraints

  • Return users who have created at least one post
  • Show the highest post counts first and resolve ties consistently

Data you will use

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

users

  • user_idINTEGER
  • usernameVARCHAR(50)
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(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

Post counts come from counting rows in the posts table per user. User names are in users. JOIN on user_id, then GROUP BY user to count their posts.

Hint 2

INNER JOIN users to posts on user_id. GROUP BY user_id. COUNT(p.post_id) AS post_count. INNER JOIN already excludes zero-post users. ORDER BY post_count DESC.

Hint 3

Start with the tables that establish the result grain for question 2, 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
SELECT u.username, u.first_name || ' ' || u.last_name AS full_name, COUNT(p.post_id) AS post_count FROM users u INNER JOIN posts p ON u.user_id = p.user_id GROUP BY u.user_id, u.username, u.first_name, u.last_name HAVING COUNT(p.post_id) >= 1 ORDER BY post_count DESC;

Why this works

INNER JOIN automatically excludes alex_brown who has zero posts. GROUP BY must include all non-aggregated SELECT columns to satisfy SQL rules.

Success check

4 users — john_doe and jane_smith (2 posts each), mike_wilson and sarah_jones (1 each)

Expected result

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

usernamefull_namepost_count
john_doeJohn Doe2
jane_smithJane Smith2
mike_wilsonMike Wilson1
sarah_jonesSarah Jones1

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.