CTEs (Common Table Expression)

Common Table Expressions (CTEs) are one of the most powerful and readable features in SQL. They help break down complex queries, improve code readability, and allow the use of recursion. In technical interviews, especially with top companies like Deloitte, TCS, Infosys, and Accenture, CTE-related questions are frequently asked to assess your problem-solving skills and query structuring ability.

What is a CTE?

A CTE (Common Table Expression) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH keyword.

Syntax of a CTE

WITH CTE_Name AS (
    -- Your SQL query here
    SELECT column1, column2
    FROM TableName
    WHERE condition
)
SELECT * FROM CTE_Name;

Why Use CTEs?

• To simplify complex joins and subqueries
• Improve readability and maintainability
• Enable recursive operations (e.g., hierarchical data)
• Can be self-referenced in recursive CTEs

Example 1: Basic CTE to Simplify a Query

Problem: Get employees whose salary is above average.

WITH AvgSalaryCTE AS (
  SELECT AVG(Salary) AS AvgSal FROM Employees
)
SELECT e.Name, e.Salary
FROM Employees e, AvgSalaryCTE a
WHERE e.Salary > a.AvgSal;

✅ Instead of writing the AVG as a subquery, we make the query more readable.

Example 2: Recursive CTE to Handle Hierarchies

Problem: Get a hierarchical list of employees reporting to a manager

WITH EmployeeHierarchy AS (
  SELECT EmployeeID, Name, ManagerID, 0 AS Level
  FROM Employees
  WHERE ManagerID IS NULL

  UNION ALL

  SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
  FROM Employees e
  JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

✅ This is especially useful for displaying org charts or processing file system structures.

Example 3: Find the Second-Highest Salary.

Using DISTINCT and ORDER BY with OFFSET (SQL Server / PostgreSQL):

SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

Using ROW_NUMBER() (works in most RDBMS):

WITH RankedSalaryCTE AS (
  SELECT Name, Salary
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rowum
FROM Employees
)
SELECT Name, Salary
FROM RankedSalaryCTE
WHERE RowNum = 2;

Example 4: Write a Query to Get All EVEN Position Records.

WITH NumberedRows AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
  FROM Employees
)
SELECT *
FROM NumberedRows
WHERE RowNum % 2 = 0;

The ROW_NUMBER() that we have used in the above query is known as a WINDOW Function. Let's understand each of them with an example:

Window Function.

A Window Function performs a calculation across a set of rows that are related to the current row, without collapsing them into a single output row (unlike aggregate functions).

It operates over a “window” of rows defined by the OVER() clause, allowing you to perform operations like ranking, running totals, comparisons with previous/next rows, and more — all while keeping the original row structure.

 

Example: Let’s say you want to assign a row number to each employee based on their salary within each department:

SELECT Name, Department, Salary,
       ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Employees;

• PARTITION BY Department: Groups data by department.
• ORDER BY Salary DESC: Orders rows within each partition.
• ROW_NUMBER(): Assigns a unique number starting from 1 in each group.

Common Window Functions:

• ROW_NUMBER() – Unique sequence number
• RANK() / DENSE_RANK() – Ranking with or without gaps
• LAG() / LEAD() – Compare with previous/next row
• SUM(), AVG(), COUNT() – Running totals over a window