LAG and LEAD for row comparison in PostgreSQL - Mini Project: Build & Apply

Using LAG and LEAD for Row Comparison in PostgreSQL

📖 Scenario: You work for a retail company that tracks daily sales. You want to compare each day's sales with the previous and next day to see trends.

🎯 Goal: Create a SQL query that uses LAG and LEAD functions to compare each day's sales with the previous and next day.

📋 What You'll Learn

Create a table called daily_sales with columns sale_date (date) and amount (integer).

Insert the exact sales data for five consecutive days.

Write a query that selects sale_date, amount, previous day's amount using LAG, and next day's amount using LEAD.

Order the results by sale_date ascending.

💡 Why This Matters

🌍 Real World

Retail and sales analysts often compare daily or periodic sales to understand trends and make decisions.

💼 Career

Knowing how to use LAG and LEAD functions is valuable for data analysts and database developers to perform time-based comparisons efficiently.

1

Create the daily_sales table and insert data

Create a table called daily_sales with columns sale_date of type DATE and amount of type INTEGER. Then insert these exact rows: ('2024-06-01', 100), ('2024-06-02', 150), ('2024-06-03', 120), ('2024-06-04', 130), ('2024-06-05', 160).

PostgreSQL

-- Create table daily_sales 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 sales data.

2

Add a query to select sales data

Write a SQL query that selects sale_date and amount from the daily_sales table ordered by sale_date ascending.

PostgreSQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO daily_sales (sale_date, amount) VALUES
('2024-06-01', 100),
('2024-06-02', 150),
('2024-06-03', 120),
('2024-06-04', 130),
('2024-06-05', 160);

-- Select sale_date and amount ordered by sale_date
-- Your code here

Need a hint?

Use SELECT to get the columns and ORDER BY sale_date ASC to sort the rows by date.

3

Use LAG and LEAD to get previous and next day sales

Modify the query to also select the previous day's amount using LAG(amount) OVER (ORDER BY sale_date) as prev_amount and the next day's amount using LEAD(amount) OVER (ORDER BY sale_date) as next_amount.

PostgreSQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO daily_sales (sale_date, amount) VALUES
('2024-06-01', 100),
('2024-06-02', 150),
('2024-06-03', 120),
('2024-06-04', 130),
('2024-06-05', 160);

-- Select sale_date, amount, previous day amount as prev_amount, and next day amount as next_amount
-- Your code here

Need a hint?

Use window functions LAG and LEAD with OVER (ORDER BY sale_date) to get previous and next row values.

4

Complete the query with ordering and aliases

Ensure the query selects sale_date, amount, prev_amount, and next_amount and orders the results by sale_date ascending. The query should be complete and ready to run.

PostgreSQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO daily_sales (sale_date, amount) VALUES
('2024-06-01', 100),
('2024-06-02', 150),
('2024-06-03', 120),
('2024-06-04', 130),
('2024-06-05', 160);

-- Finalize the query with correct ordering and aliases
-- Your code here

Need a hint?

Make sure the query includes all columns and orders by sale_date.