SQL Coding Challenges

This subquery approach is universal. The inner subquery finds the absolute maximum salary, and the outer query finds the maximum salary that is strictly less than that absolute maximum, which is logically the second highest.

Both A and B are mathematically valid using correlated subqueries. Option B checks for exactly N-1 unique salaries greater than the current row's salary. If N-1 salaries are higher, the current row must be the N-th highest.

To calculate an extreme value (like a maximum) partitioned by a specific group field, you must combine the MAX() aggregate function with a matching GROUP BY clause.

Filtering based on the result of an aggregate function (like COUNT) cannot be done in a WHERE clause because it executes before rows are grouped. You must use a GROUP BY clause followed by a HAVING clause.

In relational databases, the structural absence of a value or relationship is explicitly represented by NULL. Therefore, testing for the top-tier entity in a hierarchy requires checking WHERE ManagerID IS NULL.

Window functions cannot be evaluated directly inside a WHERE clause because filtering occurs logically before window partitions are computed. You must compute the rank inside a subquery or CTE first, then filter its output.

Sorting by Salary ASC (Ascending) assigns the lowest values the top ranks (1, 2, 3...). This allows you to filter out the lowest earners by selecting ranks less than or equal to 3.

Both A and B are valid engineering approaches. A recursive CTE functions across almost all SQL dialects, whereas engines like PostgreSQL offer native utility set-returning functions like generate_series().

Aggregate functions like AVG() cannot be used directly in a WHERE clause. The average salary must first be computed using a subquery, and then each employee's salary is compared against that result.

Since managers are stored in the same Employee table, this requires a self join. The employee is joined with their manager using ManagerID, and then their salaries are compared.

A LEFT JOIN keeps every customer, even if no matching order exists. Customers without orders will have NULL values for the Order table columns, making IS NULL the correct filter.

Partitioning by DepartmentID creates independent rankings inside each department. Filtering for rank = 2 returns the second highest salary in every department.

ROW_NUMBER() assigns a sequence number to duplicate records. Keeping RowNum = 1 and deleting rows where RowNum > 1 safely removes duplicates.

The average salary must first be calculated for each department using GROUP BY. Sorting the averages in descending order and selecting the top department returns the correct result.

GROUP BY creates one group per department, and COUNT(*) counts the employees within each group.

Consecutive-row problems depend on row order rather than grouped values. Window functions such as LAG() and LEAD() allow comparison with adjacent rows to detect consecutive occurrences.

A window aggregate computes cumulative totals without collapsing rows. As each row is processed in order, the running total grows progressively.

ROW_NUMBER() ranks each customer's orders independently. Ordering by OrderDate DESC ensures the newest order receives rank 1, making it easy to retrieve the latest order for every customer.