Mock Test Sittings for Every Learner and Track — SQL Medium Problem

Problem statement

A placement-prep centre runs mock tests in a few tracks (Aptitude, DSA, SQL, …). A learner may sit a track's mock test any number of times, or never.

Return one row for every pair of a learner and a track — pairs without any sitting included — with the columns learner_id, learner_name, track and sittings (how many times that learner sat that track's test, 0 if never). Order the rows by learner_id, then by track.

Tables

Table: Learner

Primary key: learner_id.

One row per enrolled learner.

Table: Track

Primary key: track.

One row per mock-test track.

Table: MockSitting

Primary key: sitting_id.

One row per test taken. Every learner_id and track here exists in its own table.

Examples

Example 1

Learner

Track

MockSitting

Output

How to solve Mock Test Sittings for Every Learner and Track

The answer's rows are not taken from the sittings — they are every combination of a learner and a track, including combinations that never happened. Combinations are what a CROSS JOIN produces: with 3 learners and 3 tracks it yields 9 pairs, which is exactly the grid the answer needs.

Each pair then needs its count. A LEFT JOIN to MockSitting on both learner_id and track attaches every matching sitting — several rows for a pair taken repeatedly, and one row of NULLs for a pair never taken. Grouping by the pair and counting m.sitting_id gives the number of sittings: COUNT of a column skips NULLs, so an untouched pair counts 0, whereas COUNT(*) would count its padded row as 1.

Both join conditions belong in ON. Putting m.track = t.track in WHERE instead would throw away the NULL-padded rows and with them every zero.

Alternatives: a correlated COUNT(*) per pair (no padding involved, so * is right there), or counting the sittings once in a CTE and LEFT JOINing those counts to the grid with COALESCE(n, 0). The grid has learners × tracks rows, so that product bounds the cost; the counting itself is one pass over the sittings.

Reference solution (MySQL)

SELECT l.learner_id, l.learner_name, t.track, COUNT(m.sitting_id) AS sittings
FROM Learner l
CROSS JOIN Track t
LEFT JOIN MockSitting m ON m.learner_id = l.learner_id AND m.track = t.track
GROUP BY l.learner_id, l.learner_name, t.track
ORDER BY l.learner_id, t.track

Another way

SELECT l.learner_id, l.learner_name, t.track,
  (SELECT COUNT(*) FROM MockSitting m WHERE m.learner_id = l.learner_id AND m.track = t.track) AS sittings
FROM Learner l CROSS JOIN Track t
ORDER BY l.learner_id, t.track

Another way

WITH counts AS (SELECT learner_id, track, COUNT(*) AS n FROM MockSitting GROUP BY learner_id, track)
SELECT l.learner_id, l.learner_name, t.track, COALESCE(c.n, 0) AS sittings
FROM Learner l CROSS JOIN Track t
LEFT JOIN counts c ON c.learner_id = l.learner_id AND c.track = t.track
ORDER BY l.learner_id, t.track