LEAD function for next row access in SQL - Mini Project: Build & Apply

Using the LEAD Function to Access Next Row Data

📖 Scenario: You work in a sales department and have a table of monthly sales figures for different products. You want to compare each month's sales with the next month's sales to see how sales are changing over time.

🎯 Goal: Build a SQL query that uses the LEAD function to show each product's sales for a month and the sales for the next month side by side.

📋 What You'll Learn

Create a table called monthly_sales with columns product (text), month (integer), and sales (integer).

Insert the exact data rows specified.

Write a query that selects product, month, sales, and the next month's sales using the LEAD function.

Order the results by product and month.

💡 Why This Matters

🌍 Real World

Sales analysts often need to compare current and future sales data to identify trends and make forecasts.

💼 Career

Knowing how to use window functions like LEAD is valuable for data analysts and database developers to write efficient and insightful queries.

1

Create the monthly_sales table and insert data

Create a table called monthly_sales with columns product (text), month (integer), and sales (integer). Then insert these exact rows: ('A', 1, 100), ('A', 2, 120), ('A', 3, 130), ('B', 1, 200), ('B', 2, 210).

SQL

-- Create the monthly_sales table and insert 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

Add a query to select all columns

Write a SQL query that selects product, month, and sales from the monthly_sales table.

SQL

CREATE TABLE monthly_sales (
    product TEXT,
    month INTEGER,
    sales INTEGER
);

INSERT INTO monthly_sales (product, month, sales) VALUES
('A', 1, 100),
('A', 2, 120),
('A', 3, 130),
('B', 1, 200),
('B', 2, 210);

-- Select product, month, and sales from monthly_sales
-- Your code here

Need a hint?

Use a simple SELECT statement to get the columns from the table.

3

Use the LEAD function to get next month's sales

Modify the query to also select the next month's sales for the same product using the LEAD function. Name this column next_month_sales. Use PARTITION BY product and ORDER BY month inside the LEAD function.

SQL

CREATE TABLE monthly_sales (
    product TEXT,
    month INTEGER,
    sales INTEGER
);

INSERT INTO monthly_sales (product, month, sales) VALUES
('A', 1, 100),
('A', 2, 120),
('A', 3, 130),
('B', 1, 200),
('B', 2, 210);

-- Select product, month, sales, and next month's sales using LEAD
-- Your code here

Need a hint?

Use the LEAD function with OVER clause specifying PARTITION BY product and ORDER BY month.

4

Order the results by product and month

Add an ORDER BY clause to the query to sort the results by product and then by month in ascending order.

SQL

CREATE TABLE monthly_sales (
    product TEXT,
    month INTEGER,
    sales INTEGER
);

INSERT INTO monthly_sales (product, month, sales) VALUES
('A', 1, 100),
('A', 2, 120),
('A', 3, 130),
('B', 1, 200),
('B', 2, 210);

-- Add ORDER BY product, month to the query
-- Your code here

Need a hint?

Use ORDER BY product, month at the end of the query to sort the results.