Capstone project

Project: a ranked learner activity report

Lesson 20 of 20 · 25 min · Module 6: Trustworthy database features

Produce a report that preserves quiet learners as well as active ones. Add ranking without losing the underlying totals or breaking ties arbitrarily.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Summarize without losing unmatched learners
Apply a ranking rule independently of presentation

The idea

Use a CTE to create one summary row per learner, including learners without enrollments. Then apply DENSE_RANK to the summary. Equal completed totals share a rank, with no gaps between successive ranks.

WITH totals AS (
  -- Produce one row per learner.
  SELECT ...
)
SELECT ..., DENSE_RANK() OVER (ORDER BY lessons DESC) AS activity_rank
FROM totals;

01Choose the row that must survive

Start from learners because every learner belongs in the report. The left join keeps Nora available despite having no matching enrollment.

02Separate total calculation from ranking

The CTE turns enrollment detail into one total per learner. The outer window then ranks those totals, rather than ranking individual enrollments.

03Define ties independently

Lee and Alex both have four lessons, so both get rank two. XP determines their display order but must not split their activity rank.

Your project brief

First insert learner (6, 'Nora', 'SQL', 0) with no enrollments. Use a CTE to calculate each learner’s total completed lessons with LEFT JOIN and COALESCE. Return name, xp, total lessons, and DENSE_RANK ordered by total lessons descending. Learners with equal lesson totals share a rank. Display rows by total lessons descending, then XP descending, then name.

What the checks look for

Ranks all six learners with correct totals, ties, and ordering
Keeps Nora without inventing an enrollment
Retains all original enrollments

Try it in the editor →

Take this with you

A trustworthy report makes missing relationships, tie rules, and display order explicit.

Go a little further

Add a language-partitioned rank in a separate column, and document how a learner’s two ranks answer different questions.