Why should you care?

Multi-step report “active customers who ordered in 30 days” reads top-to-bottom in one WITH block — easier code review than nested FROM (SELECT...).

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

WITH recent AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE placed_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
)
SELECT c.customer_id, c.full_name, c.city
FROM customers c
JOIN recent r ON r.customer_id = c.customer_id;

What happened?

• CTE recent computes distinct buyers last 30 days.
• Outer query JOIN filters customers to that set.
• Same statement scope — CTE gone after query finishes.

Practice next

1. Seed orders across dates.
2. Run WITH query; verify inactive customers excluded.
3. Reference CTE twice in one query (MySQL 8 allows).
4. Chain two CTEs: WITH a AS (...), b AS (SELECT FROM a).
5. Use CTE in UPDATE with JOIN (MySQL 8.0+ patterns).

Remember

WITH names intermediate result sets. Improves readability of multi-step SELECT. MySQL 8+ supports non-recursive and recursive CTEs.

DataFlow retention report

Analyst emails weekly active buyer list from saved WITH query.

Outcome: Marketing segment matches finance definition.