Project: course report
Combine joins, CTEs, windows and CASE in a reusable reporting query.
- Join and aggregate in a CTE
- Rank the result with a window
- Label depth with CASE
A reporting query answers a real question: which courses are deepest, and how do they rank? Build it in layers so each step can be checked.
- 01Total per course
LEFT JOIN courses to lessons and aggregate lessons and minutes.
- 02Rank
Number the courses by total minutes, highest first.
- 03Label
Use CASE to describe the depth band.
- 04Check
Compare the totals with a simple GROUP BY before trusting the report.
WITH course_totals AS (
SELECT c.title,
COUNT(l.id) AS lessons,
COALESCE(SUM(l.minutes), 0) AS minutes
FROM courses c
LEFT JOIN lessons l ON l.course_slug = c.slug
GROUP BY c.id, c.title
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY minutes DESC) AS rank
FROM course_totals
)
SELECT rank, title, lessons, minutes,
CASE
WHEN minutes >= 45 THEN 'Deep'
WHEN minutes >= 30 THEN 'Solid'
ELSE 'Starter'
END AS depth
FROM ranked
ORDER BY rank;Add average minutes and filter
Extend the report with average minutes per lesson and return only courses with at least two lessons.
WITH course_totals AS (
SELECT c.title,
COUNT(l.id) AS lessons,
COALESCE(SUM(l.minutes), 0) AS minutes
FROM courses c
LEFT JOIN lessons l ON l.course_slug = c.slug
GROUP BY c.id, c.title
)Which construct names a reusable result set inside a single query?
Make it yours.
Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.
Lock in what you just learned.
Mark this lesson complete to save your place and move one step closer to the certificate.