🧱 SQL CTEs — Writing Complex Queries in Simple Steps
As SQL queries become more advanced, they can become difficult to read.
You may have:
• JOINs
• Subqueries
• GROUP BY
• CASE
• Aggregations
• Multiple calculations
all inside one query.
This is where CTEs become extremely useful.
CTE stands for: «Common Table Expression»
A CTE lets you create a temporary named result set and then use it in your main query.
Think of it as:
• Step 1 → Prepare the data
• Step 2 → Transform the data
• Step 3 → Analyze the data
• Step 4 → Return the result
🧠 1. Basic CTE Syntax
A CTE starts with:
WITH cte_name AS (
SELECT
...
FROM ...
)
SELECT *
FROM cte_name;
Example:
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_sales;
The CTE
customer_sales acts like a temporary result set for the duration of the query.📊 2. Why Use CTEs?
Without a CTE, a complex query can become difficult to understand.
For example:
SELECT
customer_id,
total_spending
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS customer_totals
WHERE total_spending > 50000;
With a CTE:
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
total_spending
FROM customer_totals
WHERE total_spending > 50000;
The second version is often much easier to read.
🧩 3. CTEs Break Complex Problems into Steps
Suppose the business question is:
«Find customers whose total spending is above the average customer spending.»
Instead of writing everything as one large nested query, break it into logical steps.
• Step 1 — Calculate spending per customer
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
• Step 2 — Calculate average spending
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
),
average_spending AS (
SELECT
AVG(total_spending) AS avg_spending
FROM customer_totals
)
• Step 3 — Compare customers with the average
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
),
average_spending AS (
SELECT
AVG(total_spending) AS avg_spending
FROM customer_totals
)
SELECT
c.customer_id,
c.total_spending
FROM customer_totals c
CROSS JOIN average_spending a
WHERE c.total_spending > a.avg_spending;
Now the logic is much easier to follow.
🔗 4. Multiple CTEs
A single query can contain multiple CTEs.
Structure:
WITH first_cte AS (
...
),
second_cte AS (
...
),
third_cte AS (
...
)
SELECT ...
FROM third_cte;
Later CTEs can reference earlier CTEs.
🏢 5. Real-World Example
Suppose an e-commerce company wants:
«Customers with spending above ₹50,000 and at least 5 orders.»
First calculate customer-level metrics:
WITH customer_metrics AS (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
order_count,
total_spending
FROM customer_metrics
WHERE order_count >= 5
AND total_spending > 50000;