Exam Attempts Grid

A school keeps a list of pupils, a list of exam subjects, and a log of every exam sitting (pupils may resit a subject several times). The head of exams wants a complete grid showing how many times every pupil has sat every subject, including the combinations they have never sat, shown as 0.

Return pupil_id, pupil_name, subject and sittings, with one row per pupil and subject pair. Order by pupil_id ascending, then subject ascending. If there are no pupils or no subjects, return no rows.

Tables

pupils — One row per pupil. Names are not unique.

exam_subjects — One row per subject the school examines.

exam_sittings — One row per exam taken; resits create extra rows.

Example

pupils

exam_subjects

exam_sittings

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Joins · problem 21 of 70 · SQLite

The idea

The grid has to contain combinations that never happened, and those combinations exist in no table. So build them: pupils CROSS JOIN exam_subjects produces every pupil and subject pair.

Next, LEFT JOIN the sittings on both keys, pupil and subject. A pair with three sittings becomes three rows. A pair with none stays as one row whose sitting columns are NULL. Grouping by pupil and subject and using COUNT(x.sitting_id) gives 3 and 0. COUNT(column) skips NULLs, while COUNT(*) would count that placeholder row as 1.

Group by pupil_id (not the name, since names repeat) and sort by pupil_id, subject. That pair is unique for each row, so the order is fully determined.

The alternative keeps the cross join and counts with a correlated subquery for each pair. A subquery COUNT(*) over no rows is 0.

Solution

SELECT p.pupil_id,
       p.pupil_name,
       s.subject,
       COUNT(x.sitting_id) AS sittings
FROM pupils AS p
CROSS JOIN exam_subjects AS s
LEFT JOIN exam_sittings AS x
  ON x.pupil_id = p.pupil_id
 AND x.subject = s.subject
GROUP BY p.pupil_id, p.pupil_name, s.subject
ORDER BY p.pupil_id, s.subject;

Another way

SELECT p.pupil_id,
       p.pupil_name,
       s.subject,
       (SELECT COUNT(*)
        FROM exam_sittings AS x
        WHERE x.pupil_id = p.pupil_id
          AND x.subject = s.subject) AS sittings
FROM pupils AS p
CROSS JOIN exam_subjects AS s
ORDER BY p.pupil_id, s.subject;

Why common answers fail

COUNT(*) counts the NULL row that the LEFT JOIN produces, so a subject never sat shows 1 instead of 0.

SELECT p.pupil_id, p.pupil_name, s.subject, COUNT(*) AS sittings
FROM pupils AS p
CROSS JOIN exam_subjects AS s
LEFT JOIN exam_sittings AS x
  ON x.pupil_id = p.pupil_id AND x.subject = s.subject
GROUP BY p.pupil_id, p.pupil_name, s.subject
ORDER BY p.pupil_id, s.subject;

Without the subjects list there is nothing to produce the never-sat combinations, so the zero rows are missing.

SELECT p.pupil_id, p.pupil_name, x.subject, COUNT(x.sitting_id) AS sittings
FROM pupils AS p
LEFT JOIN exam_sittings AS x ON x.pupil_id = p.pupil_id
GROUP BY p.pupil_id, p.pupil_name, x.subject
ORDER BY p.pupil_id, x.subject;

Groups by name instead of id, so two pupils with the same name are merged into one row.

SELECT p.pupil_id, p.pupil_name, s.subject, COUNT(x.sitting_id) AS sittings
FROM pupils AS p
CROSS JOIN exam_subjects AS s
LEFT JOIN exam_sittings AS x
  ON x.pupil_id = p.pupil_id AND x.subject = s.subject
GROUP BY p.pupil_name, s.subject
ORDER BY p.pupil_id, s.subject;

What the interviewer is checking

Tests whether you can produce rows for events that did not happen, which needs a dimension cross join, and whether you know COUNT(*) versus COUNT(column) after an outer join.

Mistakes to avoid

• Starting from the sittings table, which can never produce zero rows.
• COUNT(*) after a LEFT JOIN, which turns 0 into 1.
• Joining sittings on pupil only, which counts every subject in every cell.

Follow-ups to expect

• Show only the pairs never attempted (HAVING COUNT(x.sitting_id) = 0).
• Pivot subjects into columns (SUM(CASE WHEN subject = ... THEN 1 ELSE 0 END)).
• Restrict to sittings this year without losing zero rows (put the date filter in the ON clause).

In real work

Dense report grids such as store by day sales, user by feature usage or team by week tickets, where missing combinations must show as zero rather than vanish.

Practice next