Q16 of 26 · SQL
What is a CTE (Common Table Expression) and how does it make complex QA queries more readable?
SQLMidsqlctecommon-table-expressionreadabilityadvanced
Short answer
Short answer: A CTE is a named temporary result set defined with WITH … AS (…) at the top of a query. It makes multi-step queries readable by letting you name intermediate results instead of nesting subqueries.
Detail
Without CTEs, complex validation queries become deeply nested and hard to debug. CTEs let you build the logic step by step:
-- Find customers who placed orders in the last 30 days
-- but whose most recent order is still 'pending' after 48 hours.
WITH recent_orders AS (
SELECT customer_id, MAX(created_at) AS last_order_at
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY customer_id
),
stale_pending AS (
SELECT o.customer_id, o.order_id, o.created_at
FROM orders o
JOIN recent_orders r ON r.customer_id = o.customer_id
WHERE o.status = 'pending'
AND o.created_at < NOW() - INTERVAL '48 hours'
)
SELECT u.email, sp.order_id, sp.created_at
FROM stale_pending sp
JOIN users u ON u.id = sp.customer_id;
Key benefits for QA:
- Debugging — you can run each CTE independently by temporarily selecting from it directly.
- Reuse — reference the same CTE multiple times in the outer query without duplicating the subquery.
- Readability — names like
recent_ordersorduplicate_emailscommunicate intent.
Most databases (PostgreSQL, MySQL 8+, SQL Server, SQLite 3.35+) support CTEs. MySQL 5.x does not.
// EXAMPLE
WITH duplicate_emails AS (
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
)
SELECT u.id, u.email, u.created_at
FROM users u
JOIN duplicate_emails d ON d.email = u.email
ORDER BY u.email, u.created_at;// WHAT INTERVIEWERS LOOK FOR
WITH syntax. Explanation of readability improvement over nested subqueries. At least one multi-step QA validation example.