Drill 20: Teach it — Subqueries and CTEs

SELECT course_slug, COUNT(*) AS lessons
FROM lessons
GROUP BY course_slug
HAVING COUNT(*) > (
  SELECT AVG(lesson_count)
  FROM (
    SELECT COUNT(*) AS lesson_count
    FROM lessons
    GROUP BY course_slug
  )
);

WITH totals AS (
  SELECT course_slug, SUM(minutes) AS minutes
  FROM lessons
  GROUP BY course_slug
)
SELECT course_slug, minutes
FROM totals
WHERE minutes > 30
ORDER BY minutes DESC;