NTH_VALUE function in SQL - Mini Project: Build & Apply

Using the NTH_VALUE Function in SQL

📖 Scenario: You work in a sales department and want to analyze sales data to find specific sales amounts for each salesperson.

🎯 Goal: Build a SQL query that uses the NTH_VALUE function to find the 2nd highest sale amount for each salesperson.

📋 What You'll Learn

Create a table called sales with columns salesperson (text) and amount (integer).

Insert the exact sales data provided.

Write a query that uses NTH_VALUE(amount, 2) to find the 2nd highest sale per salesperson.

Partition the data by salesperson and order by amount descending.

💡 Why This Matters

🌍 Real World

Sales teams often need to analyze specific ranked sales amounts per salesperson to understand performance beyond just the top sale.

💼 Career

Knowing how to use window functions like NTH_VALUE is valuable for data analysts and database developers working with complex data queries.

1

Create the sales table and insert data

Create a table called sales with columns salesperson (text) and amount (integer). Then insert these rows exactly: ('Alice', 500), ('Alice', 700), ('Alice', 600), ('Bob', 300), ('Bob', 400), ('Bob', 200).

SQL

-- Create the sales table and insert the data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the data.

2

Set up the window specification

Write a SQL query selecting salesperson and amount from sales. Define a window specification called sales_window that partitions by salesperson and orders by amount descending.

SQL

CREATE TABLE sales (salesperson TEXT, amount INTEGER);
INSERT INTO sales (salesperson, amount) VALUES
('Alice', 500),
('Alice', 700),
('Alice', 600),
('Bob', 300),
('Bob', 400),
('Bob', 200);

-- Write SELECT with window specification named sales_window
-- Your code here

Need a hint?

Use the WINDOW clause to define sales_window with partition and order.

3

Use NTH_VALUE to find the 2nd highest sale

Modify the query to select salesperson and use NTH_VALUE(amount, 2) OVER sales_window as second_highest_sale to find the 2nd highest sale amount per salesperson.

SQL

CREATE TABLE sales (salesperson TEXT, amount INTEGER);
INSERT INTO sales (salesperson, amount) VALUES
('Alice', 500),
('Alice', 700),
('Alice', 600),
('Bob', 300),
('Bob', 400),
('Bob', 200);

-- Select salesperson and NTH_VALUE(amount, 2) OVER sales_window as second_highest_sale
-- Your code here

Need a hint?

Use NTH_VALUE(amount, 2) OVER sales_window to get the 2nd highest sale per salesperson.

4

Complete the query with DISTINCT to show one row per salesperson

Wrap the previous query as a subquery and select distinct salesperson and second_highest_sale to show only one row per salesperson with their 2nd highest sale amount.

SQL

CREATE TABLE sales (salesperson TEXT, amount INTEGER);
INSERT INTO sales (salesperson, amount) VALUES
('Alice', 500),
('Alice', 700),
('Alice', 600),
('Bob', 300),
('Bob', 400),
('Bob', 200);

-- Select distinct salesperson and second_highest_sale from subquery
-- Your code here

Need a hint?

Use a subquery and SELECT DISTINCT to get one row per salesperson.