Window Functions: ROW_NUMBER, RANK, and DENSE_RANK

This chapter covers advanced SQL querying techniques essential for technical interviews. It focuses on Self-Joins—a fundamental technique for querying hierarchical relationships within a single table—and introduces data reshaping concepts like Pivoting and Unpivoting.

Note on Chapter Context: While this module is situated within the Window Functions chapter, mastering Self-Joins is a critical prerequisite. It teaches you how to relate rows to other rows within the same dataset, establishing the core mental models required for partition-based window operations.

1. The Core Problem: Hierarchical Data in a Single Table

In relational database design, we often encounter hierarchical structures where an entity relates to another entity of the same type. The most common real-world manifestation of this is the Employee-Manager relationship.

Instead of creating two separate tables (employees and managers), which would introduce redundant columns and violate normalization principles, database architects store all individuals in a single emp table.

The Schema Challenge

In this unified table, a single column (such as manager_id) acts as a foreign key pointing directly back to the primary key (emp_id) of the same table.

To answer questions like "Who is Ankit's manager?" or "Does Mohit earn more than his manager?", we cannot perform a standard join between two distinct physical tables. We must query the table against itself.

2. The Mental Model: Visualizing the Self-Join

To understand a self-join, you must bypass the physical reality of the single table and construct a virtual mental model.

Real-Life Analogy: The Dual-Role Mirror

Imagine a company directory printed on a sheet of paper. To find out who reports to whom, you make a photocopy of that exact same sheet.

• You place the original sheet on your left and label it "Employees".
• You place the photocopy on your right and label it "Managers".

You then use your left index finger to point to an employee's manager_id on the left sheet, and your right index finger to find the corresponding emp_id on the right sheet. Where your fingers match, you draw a line. This is exactly how a SQL engine processes a self-join.

The Virtual Split

Conceptually, the database engine duplicates the table in memory during query execution:

3. Naive vs. Decoupled Approach: Aliasing the Same Table

A naive attempt to join a table to itself without distinguishing the instances will result in a compilation/syntax error:

-- ERROR: Table name 'emp' specified more than once
SELECT * 
FROM emp 
INNER JOIN emp 
ON emp.manager_id = emp.emp_id;

The database engine cannot resolve which instance of emp you are referencing in the SELECT and ON clauses.

The Decoupled Solution: Explicit Aliasing

To resolve this ambiguity, you must assign distinct aliases to each instance of the table. By convention, we use:

• E (or emp) for the Employee context.
• M (or mgr) for the Manager context.

FROM emp E
INNER JOIN emp M
ON E.manager_id = M.emp_id

This tells the SQL compiler to treat E and M as two completely independent datasets, allowing you to select, filter, and compare columns across them seamlessly.

4. Step-by-Step Query Evolution: Joining Employee to Manager

Let us construct the query step-by-step to retrieve employee names alongside their respective managers' names.

Step 1: Define the Table Sources and Join Condition

We start by declaring our two virtual tables and linking the employee's manager_id to the manager's emp_id.

SELECT *
FROM emp E
INNER JOIN emp M
  ON E.manager_id = M.emp_id;

Step 2: Select and Rename Specific Columns

Selecting * will return duplicate columns (e.g., two emp_id columns, two emp_name columns). We must explicitly project the columns we need and apply descriptive aliases to make the output readable.

SELECT 
    E.emp_id   AS employee_id,
    E.emp_name AS employee_name,
    M.emp_name AS manager_name
FROM emp E
INNER JOIN emp M
  ON E.manager_id = M.emp_id;

Step 3: Add Financial Metrics

To prepare for advanced analytical questions, we pull the salaries from both the employee instance (E.salary) and the manager instance (M.salary).

SELECT 
    E.emp_id   AS employee_id,
    E.emp_name AS employee_name,
    E.salary   AS employee_salary,
    M.emp_name AS manager_name,
    M.salary   AS manager_salary
FROM emp E
INNER JOIN emp M
  ON E.manager_id = M.emp_id;

5. Practical Interview Question 1: Retrieving Manager Names

Problem Statement

Write a SQL query to return a list of all employees and their respective manager's name. If an employee does not have a manager (e.g., the CEO), they should still appear in the output with their manager listed as NULL or No Manager.

Solution Design

If we use an INNER JOIN, any employee with a NULL value in the manager_id column will be filtered out of the result set. To preserve these records, we must use a LEFT OUTER JOIN.

SQL Implementation

SELECT 
    E.emp_name AS [Employee Name],
    COALESCE(M.emp_name, 'No Manager') AS [Manager Name]
FROM emp E
LEFT JOIN emp M
  ON E.manager_id = M.emp_id;

Output Dataset

6. Practical Interview Question 2: Employees Earning More Than Their Managers

Problem Statement

Identify all employees who earn a higher salary than their direct manager. Return the employee's name, their salary, their manager's name, and the manager's salary.

Mathematical Representation

We are filtering the joined dataset to satisfy the following inequality:

Which translates in SQL to:

E.salary > M.salary

SQL Implementation

SELECT 
    E.emp_name AS employee_name,
    E.salary   AS employee_salary,
    M.emp_name AS manager_name,
    M.salary   AS manager_salary
FROM emp E
INNER JOIN emp M
  ON E.manager_id = M.emp_id
WHERE E.salary > M.salary;

Execution Walkthrough

1. Join Phase: The engine pairs each employee row with its corresponding manager row.
2. Filter Phase (WHERE): The engine evaluates the inequality row-by-row.
   • Ankit ($12,000) vs. *Rohit* ($10,000) $\rightarrow$ True (Keep)
   • Mohit ($15,000) vs. *Modit* ($14,000) $\rightarrow$ True (Keep)
   • Vikas ($11,000) vs. *Mah* ($9,000) $\rightarrow$ True (Keep)
3. Projection Phase (SELECT): The final filtered rows are displayed.

7. Visualizing the Data Flow

The following vertical workflow demonstrates how a single row from the emp table is processed through a self-join to produce the final comparison output.

Raw Record in Table 'emp'
   |
   | (Ankit, ID: 1, Manager ID: 4, Salary: 12000)
   v
Evaluated as Employee Instance (E)
   |
   | Reads E.manager_id (Value: 4)
   v
Looks up ID 4 in Manager Instance (M)
   |
   | Finds Record: (Rohit, ID: 4, Salary: 10000)
   v
Performs Inner Join Match
   |
   | Combined Row: Ankit (12k) | Rohit (10k)
   v
Evaluates WHERE Clause (12000 > 10000)
   |
   | Result: TRUE
   v
Outputs Final Row to Client

8. Tabular Synthesis of Table Aliases

When writing self-joins, it is easy to lose track of which alias represents which entity. Use this reference table to maintain clarity:

9. Transition to Data Reshaping: Pivoting and Unpivoting

In modern data warehousing and business intelligence, data is often stored in highly normalized, narrow formats (rows) but must be presented in wide formats (columns) for reporting. This transition is known as Pivoting.

Conversely, Unpivoting is the process of converting columns back into rows to normalize flat files (such as Excel uploads) before loading them into a relational database.

These operations are critical for advanced analytical queries and are frequently paired with window functions to calculate period-over-period growth, running totals, and market share distributions.

10. Summary and Key Takeaways

• Self-Joins are Virtual Joins: A self-join is not a distinct type of join (like INNER or LEFT); it is simply a standard join pattern where a table is joined to itself using explicit aliasing.
• Aliasing is Mandatory: You must assign unique aliases (e.g., E and M) to the table instances to avoid namespace collisions and allow the SQL compiler to resolve column references.
• Foreign Key to Primary Key: The join condition in a self-join typically maps a hierarchical foreign key column (e.g., manager_id) to the primary key column (e.g., emp_id) of the same table.
• Outer Joins Preserve Hierarchical Roots: Use a LEFT OUTER JOIN when you need to retain top-level entities (like the CEO or root departments) that do not have a parent ID.
• Row-Level Comparisons: Once the self-join is established, you can perform direct row-level comparisons between related entities, such as evaluating if an employee's salary exceeds their manager's salary ($E.salary > M.salary$).
• Pivoting and Unpivoting: These operations reshape data between wide (reporting-friendly) and narrow (database-friendly) formats, serving as a key bridge between transactional processing and analytical reporting.