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

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

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

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

Hint

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

Practice

1. What is the main purpose of the LAG function in PostgreSQL?

easy

A. To sort rows in ascending order

B. To access data from the next row in the same result set

C. To delete duplicate rows

D. To access data from a previous row in the same result set

Solution

1. Step 1: Understand LAG function behavior

   The LAG function returns the value from a previous row based on the current row's position.

2. Step 2: Compare with other options

   LEAD accesses the next row, sorting and deleting duplicates are unrelated to LAG.

3. Final Answer:

   To access data from a previous row in the same result set -> Option D

4. Quick Check:

   LAG = previous row data access [OK]

Hint: LAG looks backward, LEAD looks forward in rows [OK]

Common Mistakes:

• Confusing LAG with LEAD
• Thinking LAG sorts rows
• Assuming LAG deletes duplicates

2. Which of the following is the correct syntax to use LEAD to get the next row's salary ordered by employee_id?

easy

A. LEAD(salary) OVER (ORDER BY employee_id)

B. LEAD(salary ORDER BY employee_id)

C. LEAD(salary) ORDER BY employee_id

D. LEAD(ORDER BY salary) OVER (employee_id)

Solution

1. Step 1: Recall correct LEAD syntax

   The correct syntax uses LEAD(column) OVER (ORDER BY column) to specify the column and ordering.

2. Step 2: Check each option

   LEAD(salary) OVER (ORDER BY employee_id) matches the correct syntax; others misuse parentheses or clause order.

3. Final Answer:

   LEAD(salary) OVER (ORDER BY employee_id) -> Option A

4. Quick Check:

   LEAD(column) OVER (ORDER BY column) [OK]

Hint: LEAD/LAG need OVER with ORDER BY inside parentheses [OK]

Common Mistakes:

• Placing ORDER BY outside OVER clause
• Missing parentheses around ORDER BY
• Putting ORDER BY inside function parameters

3. Given the table sales with columns month and revenue, what will this query return?

SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM sales;

medium

A. Each row shows only current month revenue

B. Each row shows current month revenue and next month's revenue

C. Each row shows current month revenue and previous month's revenue

D. Query will cause a syntax error

Solution

1. Step 1: Understand LAG with ORDER BY month

   The LAG(revenue) returns the revenue from the previous month ordered by month.

2. Step 2: Interpret query output

   Each row shows current month revenue and previous month's revenue in prev_revenue. The first row's prev_revenue will be NULL.

3. Final Answer:

   Each row shows current month revenue and previous month's revenue -> Option C

4. Quick Check:

   LAG = previous row value [OK]

Hint: LAG returns previous row's value based on ORDER BY [OK]

Common Mistakes:

• Confusing LAG with LEAD
• Expecting next month's revenue instead
• Thinking query causes error

4. Identify the error in this query:

SELECT employee_id, salary, LEAD(salary) ORDER BY employee_id FROM employees;

medium

A. ORDER BY should be inside LEAD() function

B. Missing OVER() clause for LEAD function

C. LEAD cannot be used with salary column

D. employee_id must be in GROUP BY clause

Solution

1. Step 1: Check LEAD function syntax

   LEAD requires an OVER() clause to specify ordering and partitioning.

2. Step 2: Analyze query structure

   The query uses LEAD(salary) without OVER(), causing a syntax error.

3. Final Answer:

   Missing OVER() clause for LEAD function -> Option B

4. Quick Check:

   Window functions need OVER() [OK]

Hint: Always use OVER() with LEAD/LAG [OK]

Common Mistakes:

• Omitting OVER() clause
• Placing ORDER BY outside OVER()
• Confusing window functions with aggregates

5. You want to find all employees whose salary increased compared to the previous employee ordered by hire_date. Which query correctly uses LAG to achieve this?

hard

A. SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary;

B. SELECT employee_id, salary FROM employees WHERE salary > (SELECT LAG(salary) FROM employees ORDER BY hire_date);

C. SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees WHERE salary > prev_salary;

D. SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees HAVING salary > prev_salary;

Solution

1. Step 1: Understand LAG usage for filtering

   Window functions like LAG cannot be used directly in WHERE clause; use subquery or CTE to compute then filter.

2. Step 2: Analyze options

   SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary; correctly uses subquery. The second option's subquery LAG lacks OVER clause (syntax error). The third uses alias in WHERE (invalid). The fourth uses HAVING without GROUP BY (invalid).

3. Final Answer:

   SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary; -> Option A

4. Quick Check:

   Subquery to filter on window functions [OK]

Hint: Use subquery to filter on window function results [OK]

Common Mistakes:

• Using alias in WHERE clause
• Misusing HAVING without GROUP BY
• Incorrect subquery for LAG