CTEs can …

  • aid readability of a query
  • be used as an alternative to derived tables (aka nested subqueries)
  • be referenced multiple times
  • be used for recursive queries

Here are some use cases.

Filtering or Grouping on Windowing Function

Here are the top two cities by population for each state in the table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

DECLARE @CityList TABLE (
	City VARCHAR(30) NOT NULL,
	StateCode CHAR(2) NOT NULL,
	[Population] INT NOT NULL
);

INSERT INTO @CityList (City, StateCode, [Population])
VALUES
('Portland', 'ME', 66363),
('Lewiston', 'ME', 36491),
('Bangor', 'ME', 33011),
('South Portland', 'ME', 25068),
('Manchester', 'NH', 109830),
('Nashua', 'NH', 86704),
('Concord', 'NH', 42733),
('Derry', 'NH', 33109),
('Dover', 'NH', 30168),
('Rochester', 'NH', 29935),
('Merrimack', 'NH', 25494),
('Boston', 'MA', 625087),
('Worcester', 'MA', 181631),
('Springfield', 'MA', 153155),
('Lowell', 'MA', 107584),
('Cambridge', 'MA', 106038),
('New Bedford', 'MA', 95183),
('Brockton', 'MA', 94316),
('Quincy', 'MA', 92909);

WITH CityRank AS
(  
	SELECT 
		ROW_NUMBER() OVER(PARTITION BY StateCode ORDER BY [Population] DESC) AS PopulationOrder,
		City,
		StateCode,
		[Population]
	FROM
		@CityList 
)
SELECT
	City,
	StateCode,
	[Population]
FROM
	CityRank
WHERE
	PopulationOrder <= 2
ORDER BY
	StateCode,
	PopulationOrder;

Nested Aggregate Functions

Here it’s using the same numbers as Population above but let’s call it TotalSales for this example, and we will calculate the average sales for a state.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

DECLARE @Sales TABLE (
	City VARCHAR(30) NOT NULL,
	StateCode CHAR(2) NOT NULL,
	TotalSales INT NOT NULL
);

INSERT INTO @Sales (City, StateCode, TotalSales)
VALUES
('Portland', 'ME', 66363),
('Lewiston', 'ME', 36491),
('Bangor', 'ME', 33011),
('South Portland', 'ME', 25068),
('Manchester', 'NH', 109830),
('Nashua', 'NH', 86704),
('Concord', 'NH', 42733),
('Derry', 'NH', 33109),
('Dover', 'NH', 30168),
('Rochester', 'NH', 29935),
('Merrimack', 'NH', 25494),
('Boston', 'MA', 625087),
('Worcester', 'MA', 181631),
('Springfield', 'MA', 153155),
('Lowell', 'MA', 107584),
('Cambridge', 'MA', 106038),
('New Bedford', 'MA', 95183),
('Brockton', 'MA', 94316),
('Quincy', 'MA', 92909);

WITH SalesByState AS
(  
	SELECT 
		StateCode,
		SUM(TotalSales) AS TotalStateSales
	FROM
		@Sales
	GROUP BY
		StateCode
)
SELECT
	AVG(TotalStateSales) AS AvgStateSales
FROM
	SalesByState;

Recursive Queries

Here we have one of the classic recursive examples, a list of employees and their managers.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

DECLARE @Employees TABLE (
  EmployeeId INT NOT NULL PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  ManagerId INT NULL
);

INSERT INTO @Employees(EmployeeId, FirstName, ManagerId)
VALUES
(1, 'John', NULL),
(2, 'Phil', 1),
(3, 'Julie', 1),
(4, 'Andy', 1),
(5, 'Barbara', 2),
(6, 'Jeremy', 2),
(7, 'Jade', 4),
(8, 'Jo', 4),
(9, 'Denise', 4),
(10, 'Jim', 5);


WITH OrgChart (EmployeeId, EmployeeName, ManagerId, ManagerName, EmpLevel) AS
(
	SELECT
		EmployeeId,
		FirstName AS EmployeeName,
		ManagerId,
		CAST(NULL AS VARCHAR(50)) AS ManagerName,
		1 AS EmpLevel
	FROM
		@Employees
	WHERE
		ManagerId IS NULL

	UNION ALL

	SELECT
		e.EmployeeId,
		e.FirstName AS EmployeeName,
		e.ManagerId,
		oc.EmployeeName AS ManagerName,
		oc.EmpLevel + 1 AS EmpLevel
	FROM
		@Employees AS e
		INNER JOIN OrgChart AS oc ON oc.EmployeeId = e.ManagerId
)
SELECT
	EmployeeName,
	ManagerName,
	EmpLevel
FROM
	OrgChart;