Meta-style Company ChallengeMediumVerified answerSQLite live

Group Activity Ranking

Rank all groups by the total number of posts created by their members.

  • Window functions
  • Joins
  • Aggregation
  • Sorting

Challenge brief

Understand the request

Communities Team needs to understand which groups generate the most content activity from their members to prioritise moderation resources.

Show each group with its member count, total member posts, and an activity rank using a window function.

Return

  • group_name
  • member_count
  • total_member_posts
  • activity_rank (RANK by post count desc)

Constraints

  • Include groups whose members have created no posts
  • Users who belong to multiple groups contribute their posts to each group
  • Groups with equal totals share a rank; resolve display ties by group name

Data you will use

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

groups

  • group_idINTEGER
  • group_nameVARCHAR(100)
  • member_countINTEGER

group_members

  • group_idINTEGER
  • user_idINTEGER

posts

  • post_idINTEGER
  • user_idINTEGER

Hints, when you need them

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

Hint 1

The path is: groups to group_members (who is in each group), then group_members to posts (what those users posted). Use LEFT JOINs so groups with few posts still appear. RANK() OVER ranks without collapsing rows.

Hint 2

LEFT JOIN groups to group_members on group_id. LEFT JOIN posts on group_members.user_id = posts.user_id. GROUP BY group. COUNT(p.post_id). RANK() OVER (ORDER BY COUNT(p.post_id) DESC).

Hint 3

Start with the tables that establish the result grain for question 10, 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 g.group_name, g.member_count, COUNT(p.post_id) AS total_member_posts, RANK() OVER (ORDER BY COUNT(p.post_id) DESC) AS activity_rank FROM groups g LEFT JOIN group_members gm ON g.group_id = gm.group_id LEFT JOIN posts p ON gm.user_id = p.user_id GROUP BY g.group_id, g.group_name, g.member_count ORDER BY activity_rank, g.group_name;

Why this works

RANK() runs after GROUP BY so it ranks aggregated totals. Data Science Community has 234 members but only 1 post — member count doesn't equal activity.

Success check

3 groups — Tech Enthusiasts (5 posts, rank 1), Photography Club (2, rank 2), Data Science Community (1, rank 3)

Expected result

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

group_namemember_counttotal_member_postsactivity_rank
Tech Enthusiasts15051
Photography Club8922
Data Science Community23413

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.