Meta-style Company ChallengeMediumVerified answerSQLite live

Power Users (Above Average Posters)

Which users post more than the platform average, and by how much do they exceed it?

  • Joins
  • Subqueries
  • Aggregation
  • Numeric functions
  • Filtering

Challenge brief

Understand the request

Creator Growth Team wants to identify power creators who publish more than average, as candidates for the early-access monetisation programme.

Find users with above-average post counts, showing their count, the platform average, and the difference.

Return

  • username
  • post_count
  • avg_post_count (platform average, rounded 2 decimals)
  • difference_from_avg

Constraints

  • Compare every user against the overall average post count, including zero-post users in that average
  • Return users strictly above the average
  • Resolve equal post counts by user ID
  • Show higher post counts first; resolve ties by user ID

Data you will use

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

users

  • user_idINTEGER
  • usernameVARCHAR(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

You need two values in the same row: the user's post count AND the platform average. Build both in CTEs, then use CROSS JOIN to attach the single-row average to every user row.

Hint 2

CTE user_posts: LEFT JOIN users to posts, GROUP BY user, COUNT posts. CTE avg_posts: AVG(post_count) from user_posts. Main: CROSS JOIN avg_posts, WHERE post_count > avg_post_count.

Hint 3

Start with the tables that establish the result grain for question 8, 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 user_posts AS (SELECT u.user_id, u.username, COUNT(p.post_id) AS post_count FROM users u LEFT JOIN posts p ON u.user_id = p.user_id GROUP BY u.user_id, u.username), avg_posts AS (SELECT AVG(post_count) AS avg_post_count FROM user_posts) SELECT up.username, up.post_count, ROUND(ap.avg_post_count, 2) AS avg_post_count, up.post_count - ROUND(ap.avg_post_count, 2) AS difference_from_avg FROM user_posts up CROSS JOIN avg_posts ap WHERE up.post_count > ap.avg_post_count ORDER BY up.post_count DESC, up.user_id ASC;

Why this works

CROSS JOIN of a single-row CTE attaches the average to every user row — the classic scalar-broadcast pattern. Platform average = 6 posts / 5 users = 1.2. Only john_doe and jane_smith (2 posts each) exceed it.

Success check

2 users — john_doe and jane_smith (each 2 posts vs 1.2 average, +0.8 difference)

Expected result

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

usernamepost_countavg_post_countdifference_from_avg
john_doe21.20.8
jane_smith21.20.8

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.