Self-Joins: Mapping Employee-Manager Hierarchies

This chapter explores advanced SQL querying techniques, focusing on the crucial distinction between WHERE and HAVING clauses, solving top-$N$ per group problems using window functions (ROW_NUMBER, RANK, DENSE_RANK), and handling data granularity during technical interviews.

1. Why Do We Need This? (The Problem of Group-Level Filtering)

When querying aggregated data in SQL, developers often encounter a fundamental architectural challenge: filtering rows before aggregation versus filtering rows after aggregation.

• The Status Quo Trap: Applying aggregate conditions directly inside a WHERE clause results in syntax errors because the database engine evaluates WHERE rows before grouping occurs.
• The Granularity Challenge: In interview settings, basic queries like "find the highest-paid employee" quickly escalate to "find the top $N$ employees within each department." Standard ORDER BY and LIMIT clauses fail here because they operate on the entire result set globally, rather than partitioned by category.

2. Core Mental Model: The Filtering Pipeline

To understand how SQL executes queries containing both row-level filters and aggregate filters, visualize the execution engine processing data through a strict sequential pipeline.

Real-Life Analogy: The Quality Control Factory Line

Imagine an airport security checkpoint or a factory assembly line.

1. The WHERE Clause (The Gatekeeper): Inspects every raw item individually as it enters the conveyor belt. If an item fails basic criteria (e.g., luggage overweight), it is immediately discarded before entering the main processing hall.
2. The GROUP BY & Aggregation (The Sorter): Grouping items into batches (e.g., bins by department or category) and calculating summary metrics like averages or totals.
3. The HAVING Clause (The Supervisor): Inspects the summary metrics of the entire bin (e.g., "Does this bin's average weight exceed 10,000 kg?"). If the bin fails, the entire category is discarded.

3. WHERE vs. HAVING: Mechanics and Best Practices

Understanding where filters execute prevents silent logic bugs and optimizes query performance.

Code Example: Step-by-Step Filtering

-- Naive vs Correct Filtering Approach
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 10000            -- Step 1: Filter raw rows first
GROUP BY department_id
HAVING AVG(salary) > 12000;      -- Step 2: Filter aggregated summary next

4. Transitioning from Global Sorting to Partitioned Ranking

When interviewers ask for top-$N$ records across the entire table, standard sorting suffices. However, when the requirement shifts to top-$N$ per category, window functions become mandatory.

Scenario A: Global Top-$N$ (No Grouping)

If an interviewer asks for the top 2 highest-paid employees across the entire company, a simple ORDER BY combined with LIMIT (or TOP in SQL Server) works.

-- Global Top 2 Highest-Paid Employees
SELECT TOP 2 employee_id, name, salary
FROM employees
ORDER BY salary DESC;

Scenario B: Partitioned Top-$N$ (Per Department)

When the request demands the top 2 highest-paid employees within each department, a global sort fails because it truncates all departments except the single highest-paying one. We must use window functions.

5. Step-by-Step Evolution: Window Functions for Group-Wise Ranking

To solve department-wise rankings, we partition the dataset using window functions. This assigns a sequential rank to rows inside each distinct department partition independently.

SELECT 
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

Execution Breakdown of Window Partitioning

1. PARTITION BY department_id: Splits the employee table into virtual buckets for each department (100, 200, 300, etc.).
2. ORDER BY salary DESC: Sorts employees inside each bucket from highest salary to lowest.
3. ROW_NUMBER(): Assigns a unique sequential integer ($1, 2, 3, \dots$) to rows within that specific partition.

6. Handling Ties: ROW_NUMBER() vs. RANK() vs. DENSE_RANK()

Interviewers frequently test edge cases involving salary ties. If two employees earn the exact same salary within the same department, how should ranking behave?

Comparison of Ranking Functions

7. Real-World Practical Pitfalls & Interview Strategies

When tackling advanced SQL questions in a 3+ years experience interview, keep these core debugging and clarification strategies in mind:

• Clarify Table Granularity First: Never write code immediately. Ask the interviewer: "What is the primary key and granularity of the source table? Does one row represent an employee, or does it represent a department-project assignment?"
• Proactive Tie-Breaker Discussion: When asked to find the "top 2 employees," explicitly ask: "How should we handle salary ties? If three employees tie for the second-highest salary, should we return all three (using RANK/DENSE_RANK) or restrict strictly to two rows (using ROW_NUMBER with a secondary tie-breaker like employee_id)?"
• Subquery Wrapper Pattern: Remember that window functions (ROW_NUMBER(), RANK()) cannot be filtered directly in the WHERE clause of the same query block because of logical query processing order. You must wrap the window function query in a Common Table Expression (CTE) or derived table:

-- Production-Ready Top-N Per Category Query via CTE
WITH RankedEmployees AS (
    SELECT 
        employee_id,
        department_id,
        salary,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drnk
    FROM employees
)
SELECT employee_id, department_id, salary
FROM RankedEmployees
WHERE drnk <= 2;

8. Summary and Key Takeaways

• WHERE vs HAVING execution order: Always filter individual rows using WHERE before grouping and aggregation, and reserve HAVING exclusively for post-aggregation checks (SUM, AVG, COUNT).
• Granularity dictates architecture: Simple global queries use standard sorting (ORDER BY + LIMIT/TOP), while category-level grouping requires window functions.
• Window partitioning power: PARTITION BY divides data into logical subsets, allowing aggregate and ranking operations to execute independently per group.
• Tie handling matters: Always distinguish between ROW_NUMBER() (unique sequence), RANK() (gapped sequence on ties), and DENSE_RANK() (continuous sequence on ties).
• The Subquery/CTE Wrapper Rule: Because window functions execute after WHERE, wrap ranking queries in a CTE or derived table to filter results where rank $\le N$.
• Interview communication: Clarify table granularity and tie-breaker policies upfront to demonstrate senior engineering rigor.