Project: a course progress report

Understand the mechanism

Define grain first: one lessons row is one chapter; one completed row is one user finishing one chapter. A composite primary key prevents duplicate completion records for the same user and chapter.

WITH names intermediate results. totals counts chapters per course; done counts completed chapters for the selected user. Join those course-level summaries to courses to avoid multiplying raw detail rows.

CASE returns zero percent for a course with no chapters. 100.0 forces real division and ROUND controls display precision. Progress is chapter-based rather than time-weighted, a policy that should be explained alongside the report.

Runnable example

This course uses SQLite 3. Open a fresh in-memory database, for example with sqlite3 :memory:, and run the complete script. Each lesson supplies its own schema and sample data, independent of the website database.

PRAGMA foreign_keys=ON;
CREATE TABLE courses(id INTEGER PRIMARY KEY,name TEXT NOT NULL);
CREATE TABLE lessons(id INTEGER PRIMARY KEY,course_id INTEGER NOT NULL REFERENCES courses(id));
CREATE TABLE completed(user_id INTEGER NOT NULL,lesson_id INTEGER NOT NULL REFERENCES lessons(id),PRIMARY KEY(user_id,lesson_id));
INSERT INTO courses VALUES(1,'Python'),(2,'SQL'),(3,'Java');
INSERT INTO lessons VALUES(1,1),(2,1),(3,1),(4,2),(5,2);
INSERT INTO completed VALUES(7,1),(7,2),(8,3),(7,4);
WITH totals AS (
 SELECT course_id,COUNT(*) AS total FROM lessons GROUP BY course_id
), done AS (
 SELECT l.course_id,COUNT(*) AS finished FROM completed p
 JOIN lessons l ON l.id=p.lesson_id WHERE p.user_id=7 GROUP BY l.course_id
)
SELECT c.name,COALESCE(t.total,0) AS total,COALESCE(d.finished,0) AS finished,
 CASE WHEN COALESCE(t.total,0)=0 THEN 0.0
 ELSE ROUND(100.0*COALESCE(d.finished,0)/t.total,1) END AS percent
FROM courses c LEFT JOIN totals t ON t.course_id=c.id
LEFT JOIN done d ON d.course_id=c.id ORDER BY c.id;

Trace and verify

User 7 has Python 2/3 or 66.7%, SQL 1/2 or 50%, and Java 0/0 displayed as zero by policy. User 8’s completion does not contribute.

Common mistakes and boundaries

Production also requires users, authorization and parameter binding. Replacing 7 with a user parameter does not itself implement access control. This lesson demonstrates reporting only.

Practice challenge

Change the target user to 8, then to 9 with no completions, and predict every course result.

Reference solution and reasoning

User 8 has Python 1/3 or 33.3%, SQL 0/2 or zero, and Java zero. User 9 has zero completions everywhere; total chapters remain 3, 2 and 0 independently of the user filter.

Further reading

• SQLite SQL reference