WITH: dando um nome pra uma subquery que dá pra reaproveitar

Uma subquery que você já escreveu, sem nome

SQL: agregações usou GROUP BY com HAVING para ficar só com os grupos acima de um limite, como os cursos com mais de uma matrícula. Escrito como subquery, esse resultado é calculado uma vez e jogado fora assim que a query de fora termina. O Postgres consegue calcular a mesma subquery e dar um nome a ela: uma Common Table Expression, WITH nome AS (subquery) SELECT ... FROM nome.

WITH popular_courses AS (
  SELECT course_id FROM enrollments GROUP BY course_id HAVING count(*) > 1
)
SELECT * FROM courses JOIN popular_courses ON popular_courses.course_id = courses.id

popular_courses, o nome depois do WITH, só existe enquanto essa instrução roda. Não é uma tabela nem uma view salva, e some quando a query termina. Nas tabelas enrollments e courses deste app, a query retorna todo curso com mais de uma matrícula.

Por que um nome muda alguma coisa

Uma subquery escrita inline, (SELECT course_id FROM enrollments GROUP BY course_id HAVING count(*) > 1), precisa ser escrita por inteiro em cada lugar onde é usada: duas vezes numa query que a usa num JOIN e num WHERE, três vezes se for usada em três lugares. O WITH a escreve uma vez, sob um nome, e toda referência depois é só esse nome: uma fonte só, reaproveitada em vez de copiada.

basics, de propósito

CTEs fazem mais do que este curso cobre. Uma CTE pode referenciar a si mesma (WITH RECURSIVE), para dados em forma de árvore ou grafo, como um organograma ou as respostas de um tópico de comentários. E dá para controlar a materialização, que é o assunto da aula 6. Este curso fica na metade não recursiva: uma CTE ou várias, nenhuma referenciando a si mesma, que é o formato que você vai usar primeiro.

O que este curso cobre

Sete aulas, todas rodadas contra o banco deste app. Esta aula mostra o SQL que uma CTE produz. A aula 2 trata de ActiveRecord::Relation#with, o método do Rails para montar uma, conferido nos comentários de documentação instalados. A aula 3 mostra uma pegadinha: definir uma CTE e nunca usá-la. A aula 4 faz join com uma CTE montada a partir de dados agrupados das tabelas deste app, e a aula 5 monta uma CTE a partir de outra. A aula 6 confere com EXPLAIN o folclore de CTE mais difundido, e mais vezes errado.