Library › Coding AI › College students

Count members per club, including clubs with none

Practice: Giving the right context. About 5 minutes.

About this exercise

For the student activities app, list every chartered club (is_chartered = 1) with its member count, including clubs with zero. Both 'active' and 'pending' count (pending members were approved offline), alumni don't, and that condition must not drop zero-member clubs. Columns exactly club_name and member_count, sorted by member_count highest first, then club_name. Write one SQLite query in exactly 1 code block of at most 10 lines, under 25 words of text.

Common mistakes when you ask AI for this

  • Filtering in WHERE, or the wrong statuses. A WHERE on status drops empty clubs, and the AI can't know pending members count. Try: Add: "Put status IN ('active', 'pending') in the LEFT JOIN's ON clause."
  • COUNT(*) on a LEFT JOIN. COUNT(*) counts the empty joined row as 1, so empty clubs show 1. Try: Add: "Count a memberships column, not *."
  • Including interest groups. The AI can't know only chartered clubs belong in the app's list. Try: Add: "Only is_chartered = 1."
  • Unnamed columns and sort. The AI keeps name and an unnamed count. Try: Add: "Columns exactly club_name, member_count; ORDER BY member_count DESC, club_name."

What a strong prompt includes

  • Asks for zero-member clubs
  • Mentions the join trap
  • Limits to chartered clubs
  • Names the columns
  • Says which statuses count
  • Gives the sort

Practice this now ↑

Related exercises