Drill 16: Inputs and outputs — Project: course 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;