Fill in the Blanks: Mastering SQL CTEs


Fill in the Blanks Mastering SQL CTEsOnline version Drills to master SQL Common Table Expressions by Good Sam 1 SELECT EmployeeHierarchy e.manager_id e EmployeeHierarchy 0 eh AS e.manager_id WHERE EmployeeHierarchy level WITH NULL SELECT ON id eh.level + 1 FROM id e.name name AS INNER ALL FROM JOIN employees manager_id FROM IS manager_id RECURSIVE SELECT e.id level name eh.id UNION employees Problem 1 : Recursive Employee Hierarchy Question : For a table employees ( id INT , name VARCHAR , manager_id INT ) , write a SQL query using a CTE to list all employees along with their level in the hierarchy ( 0 for the top manager , 1 for their direct reports , etc . ) . Solution : WITH RECURSIVE EmployeeHierarchy AS ( SELECT id , name , manager_id , 0 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e . id , e . name , e . manager_id , eh . level + 1 FROM employees e INNER JOIN EmployeeHierarchy eh ON e . manager_id = eh . id ) SELECT id , name , level FROM EmployeeHierarchy ; ( , , , , , , = ) , , ; Explanation : This recursive CTE starts with the top - level manager ( where manager_id is null ) and recursively joins to its own defined result set to navigate down the employee hierarchy , incrementing the level by one with each recursion . 2 e.id = ts.emp_id total_sales JOIN BY SELECT e.name ON COALESCE LEFT GROUP sale_amount emp_id emp_id e FROM ts AS 0 WITH FROM AS TotalSales SUM SELECT TotalSales AS total_sales employees sales ts total_sales Problem 2 : Summarizing Sales Question : Calculate the total sales for each employee , assuming tables employees ( id , name ) and sales ( emp_id , sale_amount ) . Solution : WITH TotalSales AS ( SELECT emp_id , SUM ( sale_amount ) AS total_sales FROM sales GROUP BY emp_id ) SELECT e . name , COALESCE ( ts . total_sales , 0 ) AS total_sales FROM employees e LEFT JOIN TotalSales ts ON e . id = ts . emp_id ; ( , ( ) ) , ( . , ) ; Explanation : The CTE TotalSales calculates the total sales per employee . The main query then joins this CTE with the employees table to display all employees , including those with no sales ( using COALESCE to handle NULLs ) . 3 JOIN SUM WITH AS SalesRank SELECT sales FROM FROM e BY rank SELECT SalesRank sr sale_amount ON e.name e.id = sr.emp_id ORDER emp_id BY emp_id RANK() OVER sr.rank GROUP DESC AS employees Problem 3 : Ranking Sales by Employee Question : Rank employees based on their total sales in descending order . Solution : ( , ( ( ) ) ) , ; WITH SalesRank AS ( SELECT emp_id , RANK ( ) OVER ( ORDER BY SUM ( sale_amount ) DESC ) AS rank FROM sales GROUP BY emp_id ) SELECT e . name , sr . rank FROM employees e JOIN SalesRank sr ON e . id = sr . emp_id ; Explanation : The CTE SalesRank calculates the total sales for each employee and assigns a rank based on these totals . The main query joins this CTE with the employees table to list employees along with their sales rank . 4 AS total_sales total_sales AS AS MONTH WITH FROM sale_date FROM COALESCE month AS SELECT FROM AS GROUP month YEAR EXTRACT sale_date EXTRACT FROM MonthlySales sale_date 0 year BY FROM MONTH FROM EXTRACT previous_month_sales year sale_amount previous_month_sales MonthlyGrowth SUM YEAR sale_date total_sales total_sales AS BY month month year growth OVER MonthlyGrowth FROM LAG SELECT sales MonthlySales year ORDER EXTRACT total_sales AS SELECT Problem 4 : Monthly Sales Analysis Question : Analyze monthly sales growth by comparing current month's sales with the previous month . Solution : ( ( ) , ( ) , ( ) ( ) , ( ) ) , ( , , , ( ) ( , ) ) , , , ( - , ) ; WITH MonthlySales AS ( SELECT EXTRACT ( YEAR FROM sale_date ) AS year , EXTRACT ( MONTH FROM sale_date ) AS month , SUM ( sale_amount ) AS total_sales FROM sales GROUP BY EXTRACT ( YEAR FROM sale_date ) , EXTRACT ( MONTH FROM sale_date ) ) , MonthlyGrowth AS ( SELECT year , month , total_sales , LAG ( total_sales ) OVER ( ORDER BY year , month ) AS previous_month_sales FROM MonthlySales ) SELECT year , month , total_sales , COALESCE ( total_sales - previous_month_sales , 0 ) AS growth FROM MonthlyGrowth ; Explanation : Two CTEs are used here . MonthlySales calculates the total sales for each month . MonthlyGrowth then uses the LAG window function within the CTE to find the previous month's sales , and the main query calculates the growth by subtracting the previous month's sales from the current month's . 5 BY 3 e sales COUNT() > employees SELECT HAVING FROM emp_id EmployeeSalesCount GROUP AS EmployeeSalesCount sale_amount e.name ON e.id = esc.emp_id avg_sale_amount avg_sale_amount num_sales emp_id SELECT esc FROM JOIN esc AVG WITH COUNT() AS AS Problem 5 : Filtered Aggregation Question : Compute the average sales per employee only for those employees who made more than 3 sales . Solution : ( , , ( ) ) , . ; WITH EmployeeSalesCount AS ( SELECT emp_id , COUNT ( * ) AS num_sales , AVG ( sale_amount ) AS avg_sale_amount FROM sales GROUP BY emp_id HAVING COUNT ( * ) > 3 ) SELECT e . name , esc . avg_sale_amount FROM employees e JOIN EmployeeSalesCount esc ON e . id = esc . emp_id ; Explanation : The CTE EmployeeSalesCount calculates the number of sales and average sale amount per employee , filtering to include only those with more than three sales . The main query then joins this CTE to the employees table to fetch the employee names and their average sales . 6 employees name LIKE 'J%' SELECT id EmployeeCTE WHERE WITH FROM EmployeeCTE FROM name AS SELECT Problem 6 : Simple CTE Question : Create a CTE that selects all employees from the employees table and then use it to select all employees whose name starts with 'J' . Solution : ( , ) * ; WITH EmployeeCTE AS ( SELECT id , name FROM employees ) SELECT * FROM EmployeeCTE WHERE name LIKE 'J%' ; Explanation : This CTE , EmployeeCTE , acts as a temporary table containing all employee records . The main query then filters this CTE for employees whose names start with 'J' . 7 RECURSIVE 10 NumberSequence number 1 WHERE FROM SELECT number + 1 FROM NumberSequence UNION WITH AS number ALL SELECT < NumberSequence SELECT AS Problem 7 : Recursive CTE Question : Write a recursive CTE to generate a sequence of numbers from 1 to 10 . Solution : ( ) * ; WITH RECURSIVE NumberSequence AS ( SELECT 1 AS number UNION ALL SELECT number + 1 FROM NumberSequence WHERE number < 10 ) SELECT * FROM NumberSequence ; Explanation : The recursive CTE starts with a base case ( selecting the number 1 ) , then recursively adds 1 to the previous number until the number 10 is reached . 8 amount SELECT AS amount SUM WITH sale_date SalesCTE BY ORDER sales AS OVER SELECT FROM running_total SalesCTE FROM sale_date Problem 8 : CTE for Running Total Question : Use a CTE to calculate the running total of sales from the sales table . Solution : WITH SalesCTE AS ( SELECT sale_date , amount , SUM ( amount ) OVER ( ORDER BY sale_date ) AS running_total FROM sales ) SELECT * FROM SalesCTE ; ( , , ( ) ( ) ) * ; Explanation : The CTE calculates the running total of sales amounts , ordered by the sale date . The window function SUM ( ) is used with the OVER clause to accumulate the sales . 9 FROM orders order_id HighValueOrders WITH order_id 1000 WHERE SET status = 'High value' HighValueOrders FROM orders WHERE order_id IN SELECT UPDATE AS total_amount > SELECT Problem 9 : CTE in UPDATE Statement Question : Use a CTE to update the status of orders in the orders table where the total amount is greater than 1000 . Solution : WITH HighValueOrders AS ( SELECT order_id FROM orders WHERE total_amount > 1000 ) UPDATE orders SET status = 'High value' WHERE order_id IN ( SELECT order_id FROM HighValueOrders ) ; ( ) ( ) ; Explanation : The CTE identifies orders with a total amount greater than 1000 . The main UPDATE query then uses this result to set the status of these orders . 10 SELECT FROM salary END SalaryCategories id THEN 50000 WHEN 100000 AS 50000 name AND category SELECT 'High' BETWEEN salary 'Medium' AS SalaryCategories 'Low' salary FROM ELSE THEN employees WITH CASE WHEN < Problem 10 : CTE for Data Partitioning Question : Partition the employees table into three categories based on salary : Low , Medium , and High . Use a CTE for classification . Solution : WITH SalaryCategories AS ( SELECT id , name , salary , CASE WHEN salary < 50000 THEN 'Low' WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium' ELSE 'High' END AS category FROM employees ) SELECT * FROM SalaryCategories ; ( , , , ) * ; Explanation : The CTE classifies each employee into a salary category based on their salary . The CASE statement is used for conditional logic within the CTE to assign categories .

1

SELECT EmployeeHierarchy e.manager_id e EmployeeHierarchy 0 eh AS e.manager_id WHERE EmployeeHierarchy level WITH NULL SELECT ON id eh.level + 1 FROM id e.name name AS INNER ALL FROM JOIN employees manager_id FROM IS manager_id RECURSIVE SELECT e.id level name eh.id UNION employees

Problem 1 : Recursive Employee Hierarchy
Question : For a table employees ( id INT , name VARCHAR , manager_id INT ) , write a SQL query using a CTE to list all employees along with their level in the hierarchy ( 0 for the top manager , 1 for their direct reports , etc . ) .

Solution :

WITH RECURSIVE EmployeeHierarchy AS (
SELECT id , name , manager_id , 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e . id , e . name , e . manager_id , eh . level + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e . manager_id = eh . id
)
SELECT id , name , level
FROM EmployeeHierarchy ;

(
, , ,

, , ,

=
)
, ,
;

Explanation : This recursive CTE starts with the top - level manager ( where manager_id is null ) and recursively joins to its own defined result set to navigate down the employee hierarchy , incrementing the level by one with each recursion .

2

e.id = ts.emp_id total_sales JOIN BY SELECT e.name ON COALESCE LEFT GROUP sale_amount emp_id emp_id e FROM ts AS 0 WITH FROM AS TotalSales SUM SELECT TotalSales AS total_sales employees sales ts total_sales

Problem 2 : Summarizing Sales
Question : Calculate the total sales for each employee , assuming tables employees ( id , name ) and sales ( emp_id , sale_amount ) .

Solution :

WITH TotalSales AS (
SELECT emp_id , SUM ( sale_amount ) AS total_sales
FROM sales
GROUP BY emp_id
)
SELECT e . name , COALESCE ( ts . total_sales , 0 ) AS total_sales
FROM employees e
LEFT JOIN TotalSales ts ON e . id = ts . emp_id ;

(
, ( )

)
, ( . , )

;

Explanation : The CTE TotalSales calculates the total sales per employee . The main query then joins this CTE with the employees table to display all employees , including those with no sales ( using COALESCE to handle NULLs ) .

3

JOIN SUM WITH AS SalesRank SELECT sales FROM FROM e BY rank SELECT SalesRank sr sale_amount ON e.name e.id = sr.emp_id ORDER emp_id BY emp_id RANK() OVER sr.rank GROUP DESC AS employees

Problem 3 : Ranking Sales by Employee
Question : Rank employees based on their total sales in descending order .

Solution :

(
, ( ( ) )

)
,

;

WITH SalesRank AS (
SELECT emp_id , RANK ( ) OVER ( ORDER BY SUM ( sale_amount ) DESC ) AS rank
FROM sales
GROUP BY emp_id
)
SELECT e . name , sr . rank
FROM employees e
JOIN SalesRank sr ON e . id = sr . emp_id ;
Explanation : The CTE SalesRank calculates the total sales for each employee and assigns a rank based on these totals . The main query joins this CTE with the employees table to list employees along with their sales rank .

4

AS total_sales total_sales AS AS MONTH WITH FROM sale_date FROM COALESCE month AS SELECT FROM AS GROUP month YEAR EXTRACT sale_date EXTRACT FROM MonthlySales sale_date 0 year BY FROM MONTH FROM EXTRACT previous_month_sales year sale_amount previous_month_sales MonthlyGrowth SUM YEAR sale_date total_sales total_sales AS BY month month year growth OVER MonthlyGrowth FROM LAG SELECT sales MonthlySales year ORDER EXTRACT total_sales AS SELECT

Problem 4 : Monthly Sales Analysis
Question : Analyze monthly sales growth by comparing current month's sales with the previous month .

Solution :

(

( ) ,
( ) ,
( )

( ) , ( )
) ,
(

,
,
,
( ) ( , )

)
, , , ( - , )
;

WITH MonthlySales AS (
SELECT
EXTRACT ( YEAR FROM sale_date ) AS year ,
EXTRACT ( MONTH FROM sale_date ) AS month ,
SUM ( sale_amount ) AS total_sales
FROM sales
GROUP BY EXTRACT ( YEAR FROM sale_date ) , EXTRACT ( MONTH FROM sale_date )
) ,
MonthlyGrowth AS (
SELECT
year ,
month ,
total_sales ,
LAG ( total_sales ) OVER ( ORDER BY year , month ) AS previous_month_sales
FROM MonthlySales
)
SELECT year , month , total_sales , COALESCE ( total_sales - previous_month_sales , 0 ) AS growth
FROM MonthlyGrowth ;
Explanation : Two CTEs are used here . MonthlySales calculates the total sales for each month . MonthlyGrowth then uses the LAG window function within the CTE to find the previous month's sales , and the main query calculates the growth by subtracting the previous month's sales from the current month's .

5

BY 3 e sales COUNT() > employees SELECT HAVING FROM emp_id EmployeeSalesCount GROUP AS EmployeeSalesCount sale_amount e.name ON e.id = esc.emp_id avg_sale_amount avg_sale_amount num_sales emp_id SELECT esc FROM JOIN esc AVG WITH COUNT() AS AS

Problem 5 : Filtered Aggregation
Question : Compute the average sales per employee only for those employees who made more than 3 sales .

Solution :

(
, , ( )

)
, .

;

WITH EmployeeSalesCount AS (
SELECT emp_id , COUNT ( * ) AS num_sales , AVG ( sale_amount ) AS avg_sale_amount
FROM sales
GROUP BY emp_id
HAVING COUNT ( * ) > 3
)
SELECT e . name , esc . avg_sale_amount
FROM employees e
JOIN EmployeeSalesCount esc ON e . id = esc . emp_id ;
Explanation : The CTE EmployeeSalesCount calculates the number of sales and average sale amount per employee , filtering to include only those with more than three sales . The main query then joins this CTE to the employees table to fetch the employee names and their average sales .

6

employees name LIKE 'J%' SELECT id EmployeeCTE WHERE WITH FROM EmployeeCTE FROM name AS SELECT

Problem 6 : Simple CTE
Question : Create a CTE that selects all employees from the employees table and then use it to select all employees whose name starts with 'J' .

Solution :

(
,

)
*

;

WITH EmployeeCTE AS (
SELECT id , name
FROM employees
)
SELECT *
FROM EmployeeCTE
WHERE name LIKE 'J%' ;
Explanation : This CTE , EmployeeCTE , acts as a temporary table containing all employee records . The main query then filters this CTE for employees whose names start with 'J' .

7

RECURSIVE 10 NumberSequence number 1 WHERE FROM SELECT number + 1 FROM NumberSequence UNION WITH AS number ALL SELECT < NumberSequence SELECT AS

Problem 7 : Recursive CTE
Question : Write a recursive CTE to generate a sequence of numbers from 1 to 10 .

Solution :

(

)
*
;

WITH RECURSIVE NumberSequence AS (
SELECT 1 AS number
UNION ALL
SELECT number + 1
FROM NumberSequence
WHERE number < 10
)
SELECT *
FROM NumberSequence ;
Explanation : The recursive CTE starts with a base case ( selecting the number 1 ) , then recursively adds 1 to the previous number until the number 10 is reached .

8

amount SELECT AS amount SUM WITH sale_date SalesCTE BY ORDER sales AS OVER SELECT FROM running_total SalesCTE FROM sale_date

Problem 8 : CTE for Running Total
Question : Use a CTE to calculate the running total of sales from the sales table .

Solution :

WITH SalesCTE AS (
SELECT sale_date , amount ,
SUM ( amount ) OVER ( ORDER BY sale_date ) AS running_total
FROM sales
)
SELECT *
FROM SalesCTE ;

(
, ,
( ) ( )

)
*
;
Explanation : The CTE calculates the running total of sales amounts , ordered by the sale date . The window function SUM ( ) is used with the OVER clause to accumulate the sales .

9

FROM orders order_id HighValueOrders WITH order_id 1000 WHERE SET status = 'High value' HighValueOrders FROM orders WHERE order_id IN SELECT UPDATE AS total_amount > SELECT

Problem 9 : CTE in UPDATE Statement
Question : Use a CTE to update the status of orders in the orders table where the total amount is greater than 1000 .

Solution :

WITH HighValueOrders AS (
SELECT order_id
FROM orders
WHERE total_amount > 1000
)
UPDATE orders
SET status = 'High value'
WHERE order_id IN ( SELECT order_id FROM HighValueOrders ) ;

(

)

( ) ;
Explanation : The CTE identifies orders with a total amount greater than 1000 . The main UPDATE query then uses this result to set the status of these orders .

10

SELECT FROM salary END SalaryCategories id THEN 50000 WHEN 100000 AS 50000 name AND category SELECT 'High' BETWEEN salary 'Medium' AS SalaryCategories 'Low' salary FROM ELSE THEN employees WITH CASE WHEN <

Problem 10 : CTE for Data Partitioning
Question : Partition the employees table into three categories based on salary : Low , Medium , and High . Use a CTE for classification .

Solution :

WITH SalaryCategories AS (
SELECT id , name , salary ,
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'High'
END AS category
FROM employees
)
SELECT *
FROM SalaryCategories ;

(
, , ,

)
*
;
Explanation : The CTE classifies each employee into a salary category based on their salary . The CASE statement is used for conditional logic within the CTE to assign categories .

Mastering SQL CTEs

Use these credentials to integrate the game into an LMS compatible with LTI 1.1 or LTI 1.3 such as Canvas, Moodle, or Blackboard. This way, the scores will be automatically saved into the platform’s gradebook.

Mastering SQL CTEs