CTEs and Recursive CTEs

Reuse a Named Result Repeatedly

A CTE names an intermediate result used within one SQL statement. Write its name and query after WITH, then reference it from the following SELECT. A recursive CTE uses WITH RECURSIVE to select base rows, then adds new rows by using those found in the previous step.

GOAL

Explain how regular and recursive CTEs are processed.

Which categories are selected first by this recursive CTE?

CHECK IT

WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS count
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM order_counts

order_counts5 rows

customer_id	count
1	3
2	1
3	2
4	1
5	1

A CTE names an intermediate result used within one SQL statement. Write its name and query after WITH, then reference the result from the following SELECT.