Meta-style Company ChallengeEasyVerified answerSQLite live

Public Groups

Which groups are open to the public, and how many members do they have?

  • Joins
  • Date analysis
  • Filtering
  • Sorting

Challenge brief

Understand the request

Communities Team wants to promote discoverable groups on the Explore tab and needs all publicly visible groups.

List public groups with group name, description, creator username, member count, and creation date.

Return

  • group_name
  • description
  • creator_username
  • member_count
  • created_at

Constraints

  • Return public groups only
  • Show the largest member counts first

Data you will use

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

groups

  • group_idINTEGER
  • group_nameVARCHAR(100)
  • descriptionTEXT
  • created_byINTEGER
  • created_atDATETIME
  • privacyVARCHAR(20)
  • member_countINTEGER

users

  • user_idINTEGER
  • usernameVARCHAR(50)

Hints, when you need them

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

Hint 1

Group data is in groups. The privacy column determines visibility — you want 'public'. Creator username is in users, connected via created_by = user_id.

Hint 2

INNER JOIN groups to users on created_by = user_id. WHERE g.privacy = 'public'. ORDER BY g.member_count DESC.

Hint 3

Start with the tables that establish the result grain for question 5, 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.description, u.username AS creator_username, g.member_count, g.created_at FROM groups g INNER JOIN users u ON g.created_by = u.user_id WHERE g.privacy = 'public' ORDER BY g.member_count DESC;

Why this works

WHERE g.privacy = 'public' filters out the Data Science Community (private). INNER JOIN brings in the creator username from users.

Success check

2 public groups — Tech Enthusiasts (150 members) and Photography Club (89). Data Science Community is 'private' and excluded.

Expected result

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

group_namedescriptioncreator_usernamemember_countcreated_at
Tech EnthusiastsGroup for tech loversjohn_doe1502021-01-15 10:00:00
Photography ClubShare your photosjane_smith892021-03-20 14:00:00

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.