Milestone: a bird-sightings leaderboard

What are you building?

The bird club runs a friendly competition each spring: who can log the most different species between 1 March and 31 May? The committee wants a board they can pin up in the hide. One row per member, the number of species each one saw in the season, best first.

There is no new SQL here. Everything you need comes from the lessons you have already done. The work is in combining them correctly, and in noticing the small decisions that turn a nearly right board into a right one. Open the schema tab and read the three tables before you type anything: members, species, and sightings, which links the other two.

• Every member appears, including people who saw nothing in the season.
• The count is of different species, so seeing the same heron twice counts once.
• Only sightings from 1 March to 31 May 2026 count.
• The board shows names, sorted by species seen, highest first, with ties in name order.

How should you plan it before typing?

Write the plan as a few plain sentences, then translate each one. Most broken leaderboards come from starting with SELECT and adding bits until the output looks plausible.

Start with the shape of the answer: one row per member. That tells you the query starts FROM members and ends with GROUP BY the member's id. Next, what gets counted: species, from sightings, counted once each. Then the filter: the season, which applies to sightings, never to members. Last, the order.

Ask one more question before you start: does a member with no spring sightings still need a row? Yes, with 0. That single sentence decides the join type and where the date test goes. Settle it now and the rest of the query falls into place.

Which earlier lessons does each part use?

If a step feels shaky, go back to the lesson it comes from and rerun its examples. They are short.

• Keys and relationships: sightings.member_id points at members.id, and sightings.species_id points at species.id.
• Keeping unmatched rows: LEFT JOIN keeps Rosa and Sam, and the ON vs WHERE trap decides whether they stay.
• Lists and ranges: BETWEEN '2026-03-01' AND '2026-05-31' covers the season, both ends included, because the dates are plain YYYY-MM-DD text.
• Counting and totals: COUNT(DISTINCT ...) counts different values and skips NULL, so a padded row counts as 0.
• Grouping rows: one group per member, grouped by m.id so two members with the same name stay separate.
• Sorting and limiting: ORDER BY species_seen DESC, m.name ranks the board and settles ties.

How do you build it step by step?

Build in small runs and look at the output each time. First, join members to their sightings and look at the raw rows. Open the Rows view: the JOIN step shows each member collecting their sightings, and Rosa arriving with a row of NULLs because she has none.

Second, add the season to the join's ON clause and run again. Sam's February and June sightings no longer attach, so he now looks like Rosa: one padded row. Asha's February red kite also drops away. Count the rows per member by eye before you go on.

Third, group by member and count. Try COUNT(*), COUNT(s.species_id) and COUNT(DISTINCT s.species_id) side by side in one query, as below. The differences between the three columns are exactly the mistakes the board must avoid. Finally, keep the right count, name it species_seen, and add the ORDER BY.

SELECT m.name,
  COUNT(*) AS all_rows,
  COUNT(s.species_id) AS sightings,
  COUNT(DISTINCT s.species_id) AS species
FROM members m
LEFT JOIN sightings s ON s.member_id = m.id
  AND s.seen_on BETWEEN '2026-03-01' AND '2026-05-31'
GROUP BY m.id;

How can you check your own work?

A board everyone trusts is worth one more minute. Check it against the raw data in at least two independent ways.

SELECT s.seen_on, sp.common_name
FROM sightings s
JOIN species sp ON sp.id = s.species_id
WHERE s.member_id = 1
ORDER BY s.seen_on;

• Count the rows. The board must have as many rows as members: run SELECT COUNT(*) FROM members and compare.
• Check one member by hand. List Asha's sightings with a plain WHERE member_id = 1 query and count her different spring species yourself.
• Check a zero. Rosa joined at the end of March and has logged nothing. She must show 0, never 1 and never be missing.
• Check a boundary. Gareth saw a dipper on 2 June. It must not count, while a sighting on 31 May would.
• Check the ties. Asha and Mei both have 4, so Asha must come first by name.

What are the common ways it goes wrong?

Each of these produces a board that looks fine at a glance. The changeNotes beside the editor point them out as you type, but it helps to recognise them on sight.

• The season test in WHERE: Rosa and Sam disappear, because the filter removes their padded rows after the join.
• COUNT(*) after a left join: Rosa and Sam show 1 instead of 0.
• No DISTINCT: Asha shows 5, because her grey heron counts on two separate days.
• Season left out: Asha shows 5 again, this time from her February red kite.
• Grouping by m.name: correct today, but two members called Mei Lin would be merged into one row.
• Sorting ascending: the leader ends up at the bottom.
• Joining species to show names without needing them, then using an inner join: members with no sightings vanish again.

Where can you take it next?

Once the board is right, try some extensions. Each one reuses ideas you know, and each has its own small trap to find.

SELECT sp.common_name
FROM species sp
LEFT JOIN sightings s ON s.species_id = sp.id
  AND s.seen_on BETWEEN '2026-03-01' AND '2026-05-31'
WHERE s.id IS NULL;

• Add birds_counted, the total of bird_count for the season. Wrap it in COALESCE(..., 0) so Rosa and Sam show 0, since a sum over nothing is NULL.
• Add each member's latest spring sighting with MAX(s.seen_on). Members with none will show NULL; decide whether that is fine on a printed board.
• Count families instead of species by joining species and counting DISTINCT sp.family. Keep that join a left join.
• List the species that nobody saw this spring: left join species to spring sightings and keep the rows where the sighting id is NULL.
• Show only members who saw at least three species, using HAVING.
• A real rank column (1, 1, 3...) needs window functions, which come later in the course.

Field notes

• Plan the shape first: one row per member means starting from members and grouping by member id.
• A LEFT JOIN keeps members with no sightings, so they still get a row on the board.
• Put the season dates in the join's ON clause so they filter sightings without dropping members.
• Use COUNT(DISTINCT s.species_id) so a species seen twice is only counted once.

Easy to get wrong

In WHERE, the date test removes members with no spring sightings. In ON, it only limits which sightings attach.

COUNT(*) counts rows, including padding and repeat sightings. The board needs different species.

Without a second sort key, members on the same count can come out in any order between runs.

Questions people ask

Why not filter the dates with WHERE and accept that zero members drop off?

Because the brief says every member appears. A member with no sightings is still taking part, and a board without them answers a different question.

Does BETWEEN include 31 May?

Yes, both ends are included. That works here because seen_on holds dates only. If it held date and time text, '2026-05-31 14:00' would sort after '2026-05-31' and be left out.

Do I need the species table at all?

Not for the count. species_id is enough to tell species apart. You need species only to show names or families.

How would I show 1st, 2nd, 3rd with shared places?

That is RANK(), a window function, covered in the lesson on ranking and running totals.