Clubs With Enough Members to Register — SQL Easy Problem

Problem statement

A college recognises a student club only when at least five different students belong to it. The membership log gets a row every time a student joins a club, so a student who left a club and rejoined appears twice for it.

Return the name of every club with at least five distinct students, in a column named club. Return the rows in any order.

Tables

Table: ClubJoin

Primary key: join_id.

One row per time a student joined a club; the same student and club can appear more than once.

Examples

Example 1

ClubJoin

Output

How to solve Clubs With Enough Members to Register

Grouping the log by club gives one group per club, and the question is a condition on each group, so it belongs in HAVING.

What to count is the point of the problem. COUNT(*) counts rows, and a student who left and rejoined has two rows — Dramatics in the example has five rows but only four different students, so it must not qualify. COUNT(DISTINCT roll_no) counts each student once per club, which is the club's real size. The comparison is >= 5 because five members is enough.

The same answer can be reached by removing the duplicates first: SELECT DISTINCT club, roll_no leaves one row per membership, after which a plain COUNT(*) per club is the number of distinct students. A correlated subquery that counts each club's distinct students works too, but runs once per row of the log and needs a DISTINCT on the outside. The grouped query reads the table once; COUNT(DISTINCT …) sorts or hashes the pairs within each group.

Reference solution (MySQL)

SELECT club
FROM ClubJoin
GROUP BY club
HAVING COUNT(DISTINCT roll_no) >= 5

Another way

SELECT club FROM (SELECT DISTINCT club, roll_no FROM ClubJoin) m GROUP BY club HAVING COUNT(*) >= 5

Another way

SELECT DISTINCT club FROM ClubJoin c WHERE (SELECT COUNT(DISTINCT roll_no) FROM ClubJoin x WHERE x.club = c.club) >= 5