What is a CTE?
SQLLevel: IntermediateSubqueries & CTEs
What is a CTE?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
A CTE (Common Table Expression) is a named temporary result set defined with WITH. It exists only for the duration of the query. It makes complex queries more readable by breaking them into named steps.
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total) AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
),
prev_month AS (
SELECT
month,
total_sales,
LAG(total_sales) OVER (ORDER BY month) AS prev_sales
FROM monthly_sales
)
SELECT
month,
total_sales,
ROUND((total_sales - prev_sales) / prev_sales * 100, 2) AS growth_pct
FROM prev_month;
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT