Common Table Expressions (CTE) in SQL Server

A CTE is a named, temporary result set that lives only for ONE statement.

WITH CteName (optional column list) AS
(
    SELECT ...
)
SELECT * FROM CteName;

Why use CTEs? Readability, reuse the same result twice in one query, step-by-step logic, recursion (next file), update/delete via CTE.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: CustomerContacts with Duplicates (for de-duplication demo)

DROP TABLE IF EXISTS dbo.CustomerContacts;

CREATE TABLE dbo.CustomerContacts
(
    ContactID   INT IDENTITY(1,1) PRIMARY KEY,
    FullName    VARCHAR(50)  NOT NULL,
    Email       VARCHAR(100) NOT NULL,
    Phone       VARCHAR(20)  NULL,
    CreatedOn   DATETIME2    NOT NULL
);

INSERT INTO dbo.CustomerContacts (FullName, Email, Phone, CreatedOn) VALUES
('Ravi Kumar',   'ravi@mail.com',   '9000000001', '2025-01-01 10:00'),
('Ravi Kumar',   'ravi@mail.com',   '9000000001', '2025-01-05 09:30'),   -- duplicate
('Sita Devi',    'sita@mail.com',   '9000000002', '2025-01-02 11:00'),
('John Peter',   'john@mail.com',   NULL,         '2025-01-03 12:00'),
('John Peter',   'john@mail.com',   '9000000003', '2025-02-10 08:15'),   -- duplicate (newer)
('John Peter',   'john@mail.com',   '9000000003', '2025-03-01 14:45'),   -- duplicate (newest)
('Fatima Khan',  'fatima@mail.com', '9000000004', '2025-01-04 16:20'),
('Arun Prakash', 'arun@mail.com',   '9000000005', '2025-01-06 13:10'),
('Sita Devi',    'sita@mail.com',   '9000000002', '2025-04-11 10:05');   -- duplicate
GO

(9 rows affected)

Part A: Basic CTE

Simple CTE: high earners

WITH HighEarners AS
(
    SELECT EmployeeID, FirstName, Salary, DepartmentID
    FROM dbo.Employees
    WHERE Salary >= 90000
)
SELECT * FROM HighEarners ORDER BY Salary DESC;

CTE with column list

WITH DeptSalary (DepartmentID, EmpCount, AvgSalary) AS
(
    SELECT DepartmentID, COUNT(*), AVG(Salary)
    FROM dbo.Employees
    GROUP BY DepartmentID
)
SELECT d.DepartmentName, ds.EmpCount, ds.AvgSalary
FROM DeptSalary ds
JOIN dbo.Departments d ON d.DepartmentID = ds.DepartmentID;

Part B: Multiple CTEs (chained, step by step logic)

Step 1: order totals -> Step 2: customer totals -> Step 3: classify customers

WITH OrderTotals AS
(
    SELECT o.OrderID, o.CustomerID, SUM(oi.LineTotal) AS OrderTotal
    FROM dbo.Orders o
    JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
    WHERE o.Status <> 'Cancelled'
    GROUP BY o.OrderID, o.CustomerID
),
CustomerTotals AS
(
    SELECT CustomerID, COUNT(*) AS Orders, SUM(OrderTotal) AS Revenue
    FROM OrderTotals
    GROUP BY CustomerID
),
Classified AS
(
    SELECT ct.*,
           CASE WHEN Revenue >= 1000000 THEN 'Gold'
                WHEN Revenue >=  400000 THEN 'Silver'
                ELSE 'Bronze' END AS Tier
    FROM CustomerTotals ct
)
SELECT c.CustomerName, cl.Orders, cl.Revenue, cl.Tier
FROM Classified cl
JOIN dbo.Customers c ON c.CustomerID = cl.CustomerID
ORDER BY cl.Revenue DESC;

Use the same CTE Twice: compare each month with company monthly average

WITH MonthlySales AS
(
    SELECT DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1) AS SaleMonth,
           SUM(TotalAmount) AS Revenue
    FROM dbo.Sales
    GROUP BY DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1)
)
SELECT m.SaleMonth, m.Revenue,
       (SELECT AVG(Revenue) FROM MonthlySales) AS AvgMonthlyRevenue,
       CASE WHEN m.Revenue > (SELECT AVG(Revenue) FROM MonthlySales) THEN 'Above Avg' ELSE 'Below Avg' END AS Flag
FROM MonthlySales m
ORDER BY m.SaleMonth;

Top 3 products by revenue in each category (CTE + ROW_NUMBER)

WITH ProductRevenue AS
(
    SELECT p.CategoryID, p.ProductName, SUM(oi.LineTotal) AS Revenue
    FROM dbo.OrderItems oi
    JOIN dbo.Products p ON p.ProductID = oi.ProductID
    GROUP BY p.CategoryID, p.ProductName
),
Ranked AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY Revenue DESC) AS rn
    FROM ProductRevenue
)
SELECT c.CategoryName, r.ProductName, r.Revenue, r.rn
FROM Ranked r
JOIN dbo.Categories c ON c.CategoryID = r.CategoryID
WHERE r.rn <= 3
ORDER BY c.CategoryName, r.rn;

Part C: Modify Data Through a CTE

Find duplicates (keep the Newest row per Email)

WITH Dups AS
(
    SELECT ContactID, FullName, Email, CreatedOn,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CreatedOn DESC) AS rn
    FROM dbo.CustomerContacts
)
SELECT * FROM Dups ORDER BY Email, rn;

DELETE duplicates through the CTE (very popular interview question)

WITH Dups AS
(
    SELECT ContactID,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CreatedOn DESC) AS rn
    FROM dbo.CustomerContacts
)
DELETE FROM Dups WHERE rn > 1;

SELECT * FROM dbo.CustomerContacts;   -- now only unique emails

(4 rows affected)

UPDATE through a CTE: give 5% raise to the lowest paid person in each department

BEGIN TRAN;
    WITH Lowest AS
    (
        SELECT EmployeeID, Salary,
               ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary) AS rn
        FROM dbo.Employees
        WHERE DepartmentID IS NOT NULL
    )
    UPDATE Lowest SET Salary = Salary * 1.05 WHERE rn = 1;

    SELECT EmployeeID, FirstName, DepartmentID, Salary FROM dbo.Employees ORDER BY DepartmentID, Salary;
ROLLBACK TRAN;   -- undo so data stays the same for other scripts

(7 rows affected)

Part D: CTE vs Derived Table vs Temp Table

- CTE        : one statement only, not stored, can be referenced many times, can be recursive
- Derived    : same as CTE but inline, cannot be referenced twice
- Temp table : stored in tempdb, can be indexed, used across many statements (see file 06)
NOTE: The statement BEFORE a WITH must end with a semicolon ( ; ).

Practice Questions

1. Using a CTE, find the month with the highest sales in each Region.
2. Using two CTEs, list sales people whose 2025 sales are above the team average.
3. Find customers whose first order was Cancelled.
4. Re-insert duplicate rows into CustomerContacts and remove them keeping the OLDEST row.
5. Show categories whose revenue share is more than 20% of total revenue.