Week 12 - SQL Chips and Chunks Newsletter

1. SQL Chip: Understanding the "Second-Highest" Challenge Finding the "second-highest" value (like salary, score, or sales) is a classic SQL puzzle! It's not as simple as just asking for the MAX(). You need to find the absolute highest first, and then find the highest among everything else. This usually involves a neat trick called a subquery, where one query helps filter another.

Example: The Salary Puzzle Imagine you have an Employees table with a salary column. If you just ask for MAX(salary), you get the highest. But how do you get the next highest without knowing what the highest is beforehand? That's where our "chip" of logic comes in: find the highest salary, then find the highest salary that is less than that highest one.

2. SQL Chunk: Finding the Second-Highest Salary with a Subquery Let's see this "second-highest" logic in action with an Employee table. We'll use a subquery to first find the absolute MAX salary, and then our outer query will look for the MAX salary that is less than that value.

SELECT MAX(salary)
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

How it works:

• SELECT MAX(salary) FROM Employee (the inner query): This part runs first. It finds the absolute highest salary from your Employee table (e.g., $90,000).

• WHERE salary < ($90,000) (the outer query uses this result): The main query then looks at all salaries and only considers those that are less than $90,000.

• SELECT MAX(salary) FROM Employee WHERE ...: Finally, it finds the highest salary among those remaining salaries (which would be your second-highest).

3. SQL Challenge: Find the Third-Highest Sales Amount! Challenge: Now that you've seen how to find the second-highest, can you adapt this concept to find the third-highest sales_amount from a Sales table?

SELECT MAX(sales_amount)
FROM Sales
WHERE sales_amount < (
    SELECT MAX(sales_amount)
    FROM Sales
    WHERE sales_amount < (SELECT MAX(sales_amount) FROM Sales)
);

4. Common SQL Mistake: Misusing DISTINCT for "Nth Highest" A common mistake when trying to find the Nth highest value (like the second-highest) is to try and use DISTINCT without properly combining it with ordering or subqueries, which can lead to incorrect results or unexpected behavior.

Wrong (Often gives the wrong 'Nth' highest or misses the point):

SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC; -- This just gives a list of unique salaries, not necessarily the 2nd highest directly

Right (Robustly finds the second-highest by excluding the top value):

SELECT MAX(salary)
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

While DISTINCT is great for unique lists, it doesn't solve the "Nth highest" problem on its own. The subquery approach is more precise for filtering values relative to others.

5. SQL in Practice: Finding the Second Most Popular Product Let's apply this concept to a real-world scenario: finding the second most popular product based on order_count from a Products table.

SELECT product_name, MAX(order_count)
FROM Products
WHERE order_count < (SELECT MAX(order_count) FROM Products);

This query quickly tells you which product is the runner-up in terms of popularity, helping with inventory or marketing decisions.

6. SQL Resource: Data Engineering Academy If you're ready to go deeper into SQL and data pipelines, the Data Engineering Academy is a top-tier, project-based program that teaches real-world SQL—including advanced concepts like window functions and powerful analytical techniques. It’s perfect for anyone looking to level up from beginner to job-ready data roles like data analyst or data engineer. Start here with my referral link: Data Engineering Academy Referral Link