DataBolt

All Lessons

Introduction to SQL

SQL Lesson 1: Your Sample Database

SQL Lesson 2: SELECT — Reading data

SQL Lesson 3: WHERE — Filtering rows

SQL Lesson 4: AND, OR, NOT

SQL Lesson 5: BETWEEN, IN, LIKE

SQL Lesson 6: NULL — The Mystery Value

SQL Lesson 7: ORDER BY

SQL Lesson 8: LIMIT & OFFSET

SQL Lesson 9: Aggregate Functions

SQL Lesson 10: GROUP BY

SQL Lesson 11: HAVING

SQL Lesson 12: INNER JOINs

SQL Lesson 13: LEFT JOINs

SQL Lesson 14: RIGHT JOINs

SQL Lesson 15: SELF JOINs

SQL Lesson 16: UNION JOINs

SQL Lesson 17: Joining Multiple Tables

SQL Lesson 18: Subqueries

SQL Lesson 19: CTEs (WITH)

SQL Lesson 20: CASE Statements

SQL Lesson 21: Window Functions

SQL Lesson 22: String Functions

SQL Lesson 23: Date & Time Functions

SQL Lesson 24: INSERT, UPDATE, DELETE

SQL Lesson 25: CREATE TABLE & DDL

SQL Lesson 26: Indexes & Performance

SQL Lesson 27: Transactions & ACID

SQL Lesson 28: SQL Execution Order

SQL Lesson 29: 50 Practice Problems

CHAPTER 20

CTEs - The WITH Clause

Named subqueries that make complex logic readable.

IN THIS CHAPTER

Defining a CTE with WITH ... AS (...)
Using multiple CTEs in sequence
Recursive CTEs for hierarchical data
CTE vs subquery — when to use each

16.1 - Basic CTEs

Each employee's salary vs their department average

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, d.avg_sal,
        e.salary - d.avg_sal AS diff
FROM employees e
JOIN dept_avg d ON e.department = d.department
ORDER BY e.department, diff DESC;

16.2 - Multiple CTEs

Customer tier classification

WITH
order_totals AS (
    SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
    FROM orders GROUP BY customer_id
),
customer_tier AS (
    SELECT customer_id, spent,
        CASE WHEN spent > 100000 THEN 'VIP'
            WHEN spent > 10000 THEN 'Regular'
            ELSE 'New' END AS tier
    FROM order_totals
)
SELECT c.name, t.spent, t.tier
FROM customers c
JOIN customer_tier t ON c.customer_id = t.customer_id
ORDER BY t.spent DESC;

✅ Pro Tips: Rule of thumb: if you need to reference the same subquery twice, use a CTE. If a subquery is used once and is simple, inline it. If it is used 3+ times, consider a view or temp table.

💡 Engineering Insight: CTEs do not always improve performance — in some databases they are materialised (computed once and cached), in others they are inlined as subqueries. PostgreSQL inlines by default; you can force materialisation with the MATERIALIZED keyword. Always check your EXPLAIN plan.

Included Tables in Exercise

Table: Orders

order_id	customer_id	order_date	total	status
1	1	2024-01-15	79999	completed
2	2	2024-01-20	3498	completed
3	1	2024-02-10	65000	completed
4	3	2024-02-14	499	pending
5	2	2024-03-01	248	completed
6	5	2024-03-15	2999	shipped
7	1	2024-04-05	149	completed

Table: Customers

customer_id	name	city	country
1	Ananya	Hyderabad	India
2	Rohan	Bangalore	India
3	Sam	Mumbai	India
4	Lisa	London	UK
5	Ravi	Delhi	India

Table: order_items

item_id	order_id	product_id	qty	unit_price
1	1	1	1	79999
2	2	3	1	2999
3	2	2	1	499
4	3	5	1	65000
5	4	2	1	499
6	5	4	2	99
7	5	6	1	50
8	6	3	1	2999

Table: products

product_id	name	category	price	stock
1	iPhone 15	Electronics	79999	50
2	SQL Book	Books	499	200
3	Headphones	Electronics	2999	75
4	Notebook	Stationery	99	500
5	Laptop	Electronics	65000	20
6	Pen Set	Stationery	149	0
7	Mouse	Electronics	599	25

Exercise 👇

Exercise:

Tasks

1.👉Create a CTE to calculate total spending per customer.

2.Create a CTE for orders where total is greater than 50000 and display them.

3.Create a CTE to count total orders per customer and show customer names with their order count.

4.Create a CTE to calculate average product price and find products priced above average.

5.Use multiple CTEs to find customers who have spent more than 50000 in total.

Stuck? Read this task's