SQL Real-World Scenarios: Nth Highest Value, Removing Duplicates, and LAG/LEAD

After spending six posts working through core SQL fundamentals (tables and keys, filtering and grouping, joins, transformations and CASE, window functions, and subqueries and CTEs), I wanted to put all of those pieces together on practical SQL problems that come up in real data work and interviews.

Here are four real-world scenarios I practiced and what I learned from comparing different solutions side by side.

Scenario 1: PARTITION BY vs. GROUP BY, Side by Side

PARTITION BY and GROUP BY often get mixed up at first because both compute aggregates per category, but their output shapes are completely different.

Using a window function with PARTITION BY:

SELECT category, AVG(unit_price) OVER(PARTITION BY category) AS 'avg_price'
FROM dim_product 
ORDER BY category;

Using a standard aggregate with GROUP BY:

SELECT category, AVG(unit_price) AS 'avg_price' 
FROM dim_product 
GROUP BY category 
ORDER BY avg_price;

Both queries calculate the exact same number (the average unit_price for each category), but look at how the rows behave:

• The PARTITION BY query preserves every single product row in dim_product and attaches that category’s average price as an extra column next to each row. If a category has 50 products, you get all 50 rows back with the category average repeated alongside each product.
• The GROUP BY query collapses the entire table down to one summary row per category. If there are 5 categories in the table, you get 5 rows back total, and individual product rows are gone.

The mental rule I use now: reach for GROUP BY when you only want a summary table, and reach for PARTITION BY when you need to compare individual rows against their group’s summary value without losing the rows themselves.

Scenario 2: Finding the Nth Highest Value (3 Approaches Compared)

Finding the $N$th highest value in a table (for example, the 5th highest product price) is one of the most common SQL problems. Trying three different approaches side by side made the tradeoffs much clearer than just memorizing a single answer.

Attempt 1: NTH_VALUE() Window Function

SELECT NTH_VALUE(unit_price, 5) OVER(
    ORDER BY unit_price DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS 'fifth_highest_price'  
FROM dim_product;

NTH_VALUE(column, N) returns the value at position N inside the current window frame. Because we expanded the frame to cover the whole table (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), it finds the 5th highest unit_price in the table, but it repeats that number across every single row of dim_product.

Technically it finds the number, but if all you want is the 5th highest price as a clean answer (rather than a column attached to 500 product rows), NTH_VALUE() is an awkward fit on its own.

Attempt 2: DENSE_RANK() Inside a Subquery

SELECT unit_price FROM (
    SELECT *, DENSE_RANK() OVER(ORDER BY unit_price DESC) AS ranking 
    FROM dim_product
) AS ranked_product 
WHERE ranking = 5;

This is much cleaner. The inner query ranks every product from highest price to lowest using DENSE_RANK(), and the outer query filters for WHERE ranking = 5.

Notice why I used DENSE_RANK() here instead of RANK() or ROW_NUMBER():

• As we saw in Part 5, RANK() skips numbers after a tie. If two products tied for 4th highest price, RANK() would assign 1, 2, 3, 4, 4, 6, meaning ranking = 5 would return zero rows!
• DENSE_RANK() never skips numbers after ties (1, 2, 3, 4, 4, 5), so ranking = 5 is guaranteed to find the 5th distinct highest price.

Attempt 3: DENSE_RANK() Inside a CTE

WITH cte_table AS (
    SELECT *, DENSE_RANK() OVER(ORDER BY unit_price DESC) AS 'ranking' 
    FROM dim_product
)
SELECT unit_price FROM cte_table WHERE ranking = 5;

This executes the exact same logic as Attempt 2, but uses a Common Table Expression (WITH cte_table AS (...)) from Part 6 so the query reads cleanly from top to bottom.

Bonus: Finding the Nth Highest Value Per Category

What if you need the 5th highest-priced product inside each category rather than across the whole table? Just add PARTITION BY category inside OVER(...):

WITH cte_table AS (
    SELECT *, DENSE_RANK() OVER(PARTITION BY category ORDER BY unit_price DESC) AS "price_rank" 
    FROM dim_product
)
SELECT * FROM cte_table WHERE price_rank = 5;

Because PARTITION BY category restarts DENSE_RANK() at 1 for every category, WHERE price_rank = 5 returns the 5th highest-priced product for each category independently. And if a question asks for the top 5 products per category instead of only the 5th, you just change WHERE price_rank = 5 to WHERE price_rank <= 5.

Scenario 3: Removing Duplicate Rows with ROW_NUMBER()

Duplicate rows sneaking into a staging table is one of the most common headaches in real data engineering and ETL pipelines.

To test this on my customers table from Part 3, I first had to drop the PRIMARY KEY constraint on id (ALTER TABLE customers DROP PRIMARY KEY;), since a primary key prevents duplicate IDs from being inserted in the first place. Once duplicate rows exist in customers, here is how to deduplicate them cleanly:

WITH cte_table AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS 'number' 
    FROM customers
)
SELECT id, name, email FROM cte_table WHERE number = 1;

Here is why ROW_NUMBER() (and not RANK() or DENSE_RANK()) is the right tool for deduplication:

1. PARTITION BY id groups identical customer IDs into the same partition.
2. Because duplicate rows have the exact same id, RANK() or DENSE_RANK() would give every duplicate row 1. ROW_NUMBER() forces a unique sequential number (1, 2, 3...) even across identical rows.
3. Filtering the CTE with WHERE number = 1 keeps the first occurrence of each id and drops every duplicate (2, 3...) after it.

Scenario 4: Handling Boundary NULLs in LAG() and LEAD()

In Part 5, we saw that LAG(col) and LEAD(col) return NULL on the very first or very last row of a table because there is no previous or next row to look at.

Both LAG() and LEAD() actually accept a third argument: a fallback default value to return instead of NULL when the window reaches the edge of the table.

Let’s set up a small weather table to see it in action:

CREATE TABLE weather(
    id INT,
    temp FLOAT
);

INSERT INTO weather
VALUES 
(5, 22),
(6, 82);

Now we query previous_day and next_day using all three arguments LAG(column, offset, default_value):

SELECT *, 
    LAG(temp, 1, 0) OVER(ORDER BY id) AS 'previous_day',
    LEAD(temp, 1, 0) OVER(ORDER BY id) AS 'next_day' 
FROM weather;

• temp: The column to pull the value from.
• 1: How many rows backward (LAG) or forward (LEAD) to look.
• 0: The default value to use when no previous or next row exists.

With our two rows in weather, the query returns:

id	temp	previous_day	next_day
5	22	0	82
6	82	22	0

• For id = 5, there is no row before it, so LAG(temp, 1, 0) falls back to the default 0 instead of returning NULL, while LEAD(temp, 1, 0) pulls 82 from the next row.
• For id = 6, LAG(temp, 1, 0) pulls 22 from the previous row, and since there is no row after id = 6, LEAD(temp, 1, 0) falls back to 0.

Whether you are comparing today’s temperature against yesterday’s or this month’s revenue against last month’s, passing a default value as the 3rd argument keeps downstream math from turning into NULL on the first or last row.