Exploring SQL Server Common Table Expressions (CTE’s) in the Manufacturing Industry

A Common Table Expression (CTE) is a temporary named result set in SQL that can be utilized within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are derived from a SELECT statement and are defined within the execution scope of a single SQL statement. CTEs are powerful tools that can simplify complex join and subquery operations, improve code readability, and provide a temporary named result set for use within a larger query. With CTEs, developers can organize and manipulate data in a more efficient and streamlined manner, allowing for faster, more accurate data retrieval. Whether you’re a seasoned developer or new to SQL, understanding and utilizing CTEs can help you build more robust and efficient applications.

Agenda

1. Introduction to Common Table Expressions (CTE’s)
2. Types of CTE’s with Coding Examples
3. Real-world Advanced Practice Questions In The Manufacturing Industry
4. Most Commonly Asked Interview Question and Answer
5. Conclusion

Introduction to Common Table Expressions (CTE’s)

A Common Table Expression (CTE) is a temporary named result set that can be used within a SELECT, INSERT, UPDATE or DELETE statement. It is derived from a SELECT statement and defined within the execution scope of a single SQL statement. CTE’s can simplify complex join and subquery operations, improve the readability of the code, and provide a temporary named result set for use within a larger query.

Types of CTE’s With Coding Examples

There are two types of CTE’s: Non-recursive CTE’s and Recursive CTE’s.

Non-recursive CTE’s

A Non-recursive CTE is used to retrieve data from a single query and return the result set as a temporary result set. The following is an example of a non-recursive CTE in the manufacturing industry:

WITH ProductionData AS (
SELECT ProductID, ProductName, ProductionDate, Quantity
FROM dbo.ManufacturingData
)
SELECT ProductID, ProductName, ProductionDate, SUM(Quantity) as TotalQuantity
FROM ProductionData
GROUP BY ProductID, ProductName, ProductionDate

The above example creates a temporary result set named ProductionData, which retrieves the data from the dbo.ManufacturingData table. The final query then calculates the total quantity of each product produced on a specific date.

Recursive CTE’s

A Recursive CTE is used to perform hierarchical or recursive queries, such as retrieving hierarchical data from a table. The following is an example of a recursive CTE in the manufacturing industry:

WITH ProductHierarchy AS (
SELECT ProductID, ProductName, ParentProductID, 0 as Level
FROM dbo.ManufacturingData
WHERE ParentProductID IS NULL
UNION ALL
SELECT d.ProductID, d.ProductName, d.ParentProductID, Level + 1
FROM dbo.ManufacturingData d
INNER JOIN ProductHierarchy h
ON d.ParentProductID = h.ProductID
)
SELECT ProductID, ProductName, ParentProductID, Level
FROM ProductHierarchy
ORDER BY Level, ProductName

The above example creates a temporary result set named ProductHierarchy, which retrieves the hierarchical data from the dbo.ManufacturingData table. The final query then displays the hierarchy of products in the manufacturing industry.

Real-World Advanced Practice Questions in the Manufacturing Industry

Script to generate tables and records:

CREATE TABLE Products (
  ProductID INT PRIMARY KEY IDENTITY(1,1),
  ProductName VARCHAR(100) NOT NULL
);

CREATE TABLE Sales (
  SalesID INT PRIMARY KEY IDENTITY(1,1),
  ProductID INT NOT NULL FOREIGN KEY REFERENCES Products(ProductID),
  Date DATE NOT NULL,
  UnitsSold INT NOT NULL
);

INSERT INTO Products (ProductName)
VALUES ('Product A'), ('Product B'), ('Product C'), ('Product D'), ('Product E');

INSERT INTO Sales (ProductID, Date, UnitsSold)
VALUES
(1, '2022-01-05', 100),
(1, '2022-01-10', 50),
(2, '2022-01-01', 75),
(2, '2022-01-15', 25),
(3, '2022-01-20', 125),
(4, '2022-02-01', 50),
(5, '2022-01-25', 200);

1. How many units of each product were sold in January 2022?

Hint: This query should use a CTE to first calculate the total units sold for each product in January 2022. The CTE is then joined with the Products table to show the product name along with the total units sold. The results are ordered by the total units sold in descending order.

WITH SalesCTE AS ( 
  SELECT ProductID, SUM(UnitsSold) AS TotalUnitsSold 
  FROM Sales 
  WHERE Date BETWEEN '2022-01-01' AND '2022-01-31' 
  GROUP BY ProductID 
) 
SELECT Products.ProductName, SalesCTE.TotalUnitsSold 
FROM Products 
JOIN SalesCTE 
ON Products.ProductID = SalesCTE.ProductID 
ORDER BY SalesCTE.TotalUnitsSold DESC; 

2. What are the total units sold for each product in the last 12 months?

Hint: This query uses a CTE to first calculate the total units sold for each product in the last 12 months. The CTE is then joined with the Products table to show the product name along with the total units sold. The results are ordered by the total units sold in descending order.

WITH SalesCTE AS ( 
  SELECT ProductID, SUM(UnitsSold) AS TotalUnitsSold 
  FROM Sales 
  WHERE Date BETWEEN DATEADD(month, -12, GETDATE()) AND GETDATE() 
  GROUP BY ProductID 
) 
SELECT Products.ProductName, SalesCTE.TotalUnitsSold 
FROM Products 
JOIN SalesCTE 
ON Products.ProductID = SalesCTE.ProductID 
ORDER BY SalesCTE.TotalUnitsSold DESC; 

3. Which products had the most units sold in the last 12 months?

Hint: This query uses a CTE to first calculate the total units sold for each product in the last 12 months. The CTE is then joined with the Products table to show the product name along with the total units sold. The results are ordered by the total units sold in descending order, and the query only returns the top 1 result. This will give us the product with the most units sold in the last 12 months.

WITH SalesCTE AS ( 
  SELECT ProductID, SUM(UnitsSold) AS TotalUnitsSold 
  FROM Sales 
  WHERE Date BETWEEN DATEADD(month, -12, GETDATE()) AND GETDATE() 
  GROUP BY ProductID 
) 
SELECT TOP 1 Products.ProductName, SalesCTE.TotalUnitsSold 
FROM Products 
JOIN SalesCTE 
ON Products.ProductID = SalesCTE.ProductID 
ORDER BY SalesCTE.TotalUnitsSold DESC; 

Most Commonly Asked Interview Question

Q: What is a Common Table Expression (CTE) in SQL Server?

A: A Common Table Expression (CTE) is a temporary named result set that can be used within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It acts as a named subquery and allows for clean and readable code by breaking down complex logic into smaller, reusable pieces.

I have used CTEs in many of my projects as a way to simplify complex logic and make my code more readable. For example, in a manufacturing industry project, I used a CTE to calculate the total units sold for each product in a given month. The CTE allowed me to reuse the same logic in multiple queries without having to write the same complex code multiple times.

Conclusion

Common Table Expressions (CTEs) are a powerful tool in SQL Server that allows for more readable and efficient code. By breaking down complex logic into reusable pieces, CTEs can simplify and streamline the process of working with data in the manufacturing industry. Whether you are a beginner or an experienced SQL Server developer, understanding and using CTEs is a valuable skill to have in your toolkit.