Subqueries and CTEs

A subquery is a query inside another query. A common table expression (CTE) names that intermediate result with WITH, which makes complex logic readable.

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;

1. 01
   Find the inner result

   Write the query that produces the numbers you need to compare.

2. 02
   Name it

   Move it into a WITH clause with a short, descriptive name.

3. 03
   Use it

   Select from the CTE as if it were a table.

4. 04
   Keep it readable

   Chain a second CTE when the next step has its own meaning.