Last Minute SQL Interview Prep Kit: Solving the Top N Earners per Department Problem

This chapter covers two of the most highly tested SQL concepts in technical interviews: pivoting data using conditional aggregation and the fundamental differences between the WHERE and HAVING clauses. Understanding how the SQL engine processes individual rows versus aggregated groups is crucial for writing performant queries and passing live coding assessments.

1. Pivoting Rows to Columns: The Power of SUM(CASE WHEN ...)

In many analytical tasks, data is stored in a "narrow" or "tall" format (entity-attribute-value model) to keep the schema flexible. However, reporting tools and business stakeholders often require a "wide" format where attributes are spread across columns.

The Pivoting Pattern

To convert rows into columns, we combine the SUM() aggregate function with a conditional CASE WHEN statement. This technique is called conditional aggregation.

-- Conceptual representation of pivoting metrics for an employee
SELECT 
    employee_id,
    SUM(CASE WHEN metric_name = 'salary' THEN metric_value ELSE 0 END) AS salary,
    SUM(CASE WHEN metric_name = 'bonus' THEN metric_value ELSE 0 END) AS bonus,
    SUM(CASE WHEN metric_name = 'hike_percent' THEN metric_value ELSE 0 END) AS hike_percent
FROM employee_earnings_log
GROUP BY employee_id
ORDER BY employee_id;

Why Use SUM with CASE WHEN?

• Aggregation Requirement: When using GROUP BY employee_id, every column in the SELECT list that is not in the GROUP BY clause must be wrapped in an aggregate function.
• Null Handling: If an employee does not have a specific metric (e.g., no bonus), the CASE statement evaluates to NULL (or 0 if explicitly handled), and the aggregate function safely ignores or sums it without breaking the query.
• Unpivoting: To perform the reverse operation (converting columns back to rows), you can use the UNION or UNION ALL operator to stack the column values back into a single attribute column.

2. The Core Problem: Row-Level Filtering vs. Group-Level Filtering

In SQL, filtering is the process of eliminating data that does not meet specific criteria. There are fundamentally only two ways to filter data:

1. WHERE Clause: Filters data before any grouping or aggregation occurs.
2. HAVING Clause: Filters data after grouping and aggregation have been performed.

3. Real-World Mental Model: The Quality Inspector vs. The Regional Manager

To easily remember when to use WHERE versus HAVING, use this organizational analogy:

The Quality Inspector (WHERE)

Imagine a factory conveyor belt carrying individual apples. A Quality Inspector stands by the belt, looks at each apple one by one, and throws away any apple that weighs less than 100 grams.

• The inspector does not care about the total weight of the box or the average weight of the batch; they only make a row-by-row, individual decision. This is the WHERE clause.

The Regional Manager (HAVING)

Now imagine the apples are packed into boxes by department/farm. A Regional Manager wants to find which farms produce high-quality yields. The manager does not look at individual apples; instead, they weigh the entire box. If the average weight of the box is less than 10 kilograms, they reject the entire box.

• The manager cannot make this decision until the apples are grouped and weighed together. This is the HAVING clause.

4. Deep Dive: Row-by-Row Filtering with the WHERE Clause

The WHERE clause evaluates conditions on individual records.

Sample Dataset: employees

Scenario: Find all employees whose salary is greater than 10,000

To solve this, the database engine must inspect each row individually.

SELECT emp_id, name, department_id, salary
FROM employees
WHERE salary > 10000;

Execution Engine Walkthrough

The engine scans the table row-by-row:

• Row 1 (Alice): Salary is 12,000 ($12,000 > 10,000 \rightarrow \text{True}$). Keep.
• Row 2 (Bob): Salary is 8,000 ($8,000 > 10,000 \rightarrow \text{False}$). Discard.
• Row 3 (Charlie): Salary is 11,000 ($11,000 > 10,000 \rightarrow \text{True}$). Keep.

The engine does not look at other rows when evaluating the current row. The time complexity of this operation on an unindexed table is $O(N)$, where $N$ is the number of rows.

5. Deep Dive: Aggregated Filtering with the HAVING Clause

The HAVING clause is used to filter groups of rows created by the GROUP BY clause. It evaluates conditions on aggregated values like SUM(), AVG(), COUNT(), MAX(), or MIN().

Scenario: Find departments whose average salary is greater than 9,500

You cannot answer this by looking at individual rows. You must group the employees by their department first, calculate the average salary for each group, and then filter out the groups that do not meet the criteria.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 9500;

Step-by-Step Execution Trace

1. Group the Data:

   • Group 100: Alice (12,000), Bob (8,000), Charlie (11,000), David (9,000)
   • Group 200: Eva (15,000), Frank (7,000), Grace (6,000), Henry (10,000)

2. Calculate Aggregates:

   • Group 100 Average:

• Group 200 Average:

3. Apply HAVING Filter (avg_salary > 9500):
   • Group 100: $10,000 > 9,500 \rightarrow \text{True}$. Keep.
   • Group 200: $9,500 > 9,500 \rightarrow \text{False}$. Discard.

Output

6. The Execution Lifecycle: Combining WHERE and HAVING

When a query contains both WHERE and HAVING clauses, they are executed in a strict, non-negotiable order. The WHERE clause is always executed before the GROUP BY clause, and the HAVING clause is always executed after.

Logical Query Processing Order

The database engine processes clauses in this order:

Scenario: Combined Filtering

Problem Statement: Find the departments whose average salary is greater than 10,000, considering only those employees who earn more than 10,000 individually.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 10000
GROUP BY department_id
HAVING AVG(salary) > 10000;

7. Step-by-Step Query Walkthrough

Let's trace the execution of the combined query above using our sample dataset of 8 employees.

Step 1: Apply WHERE salary > 10000

The database filters out any individual employee who does not earn more than 10,000.

Intermediate Dataset (Post-WHERE):

Step 2: Apply GROUP BY department_id

The remaining 3 rows are grouped by their department.

• Group 100:
  • Alice (12,000)
  • Charlie (11,000)
• Group 200:
  • Eva (15,000)

Step 3: Calculate Aggregates (AVG(salary))

• Group 100 Average:

• Group 200 Average:

Step 4: Apply HAVING AVG(salary) > 10000

The database evaluates the average of each group against the threshold of 10,000.

• Group 100: $11,500 > 10,000 \rightarrow \text{True}$. Keep.
• Group 200: $15,000 > 10,000 \rightarrow \text{True}$. Keep.

Final Output

8. Common Interview Pitfalls & Debugging Workflows

Pitfall 1: Using Aggregates in the WHERE Clause

A very common candidate mistake is trying to filter aggregated values inside the WHERE clause:

-- INVALID QUERY: Will throw a syntax/compilation error
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 10000
GROUP BY department_id;

Why it fails: The WHERE clause is executed before the data is grouped. At the time WHERE runs, the database engine does not know which rows belong to which department, making it mathematically impossible to calculate AVG(salary).

Pitfall 2: Using Non-Aggregated Columns in HAVING

Another common error is filtering on a non-aggregated column inside the HAVING clause when it is not part of the GROUP BY clause:

-- INVALID QUERY (in standard SQL databases like PostgreSQL/SQL Server)
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING salary > 10000;

Why it fails: Once data is grouped, individual row values (like salary) are collapsed. The database engine does not know which employee's salary to evaluate within the group.

Correction: If you want to filter individual salaries, use WHERE salary > 10000 before grouping. If you want to filter by group metrics, use an aggregate function like HAVING MAX(salary) > 10000.

9. Tabular Synthesis: WHERE vs. HAVING

Summary and Key Takeaways

• Pivoting with SUM(CASE WHEN...): Convert row-level attributes to columns by wrapping conditional CASE statements inside a SUM() or MAX() aggregate function grouped by the entity ID.
• Row-by-Row Filtering: The WHERE clause acts on individual records. It is evaluated during the initial table scan phase.
• Group-Level Filtering: The HAVING clause acts on aggregated groups. It is evaluated after the GROUP BY phase has completed.
• Execution Order Matters: The logical execution order is FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ HAVING $\rightarrow$ SELECT.
• Performance Best Practice: Always filter out unwanted rows as early as possible using the WHERE clause. This reduces the memory and CPU footprint required for the subsequent GROUP BY sorting and hashing operations.
• Interview Tip: If an interviewer asks you to filter based on a metric like "total sales," "average rating," or "count of orders," your mind should immediately jump to the HAVING clause. If they ask to filter by "status," "date range," or "active flag," use the WHERE clause.