LAG function for previous row access in SQL - Mini Project: Build & Apply

Using LAG Function to Access Previous Row Data

📖 Scenario: You work for a small retail company that tracks daily sales. You want to compare each day's sales with the previous day's sales to see how sales change day by day.

🎯 Goal: Build a SQL query using the LAG function to retrieve the previous day's sales amount alongside the current day's sales.

📋 What You'll Learn

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

Insert exactly these rows into daily_sales: ('2024-01-01', 100), ('2024-01-02', 150), ('2024-01-03', 120), ('2024-01-04', 130).

Write a query selecting sale_date, amount, and the previous day's amount using the LAG function.

Order the results by sale_date ascending.

💡 Why This Matters

🌍 Real World

Retail companies often compare daily sales to previous days to understand trends and make decisions.

💼 Career

Knowing how to use window functions like LAG is important for data analysts and database developers to perform time-based comparisons.

1

Create the daily_sales table and insert data

Write SQL statements to create a table called daily_sales with columns sale_date (date) and amount (integer). Then insert these exact rows: ('2024-01-01', 100), ('2024-01-02', 150), ('2024-01-03', 120), ('2024-01-04', 130).

SQL

-- Create table daily_sales and insert data
-- Your code here

Need a hint?

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

2

Set up the query to select sales data

Write a SQL SELECT statement to get sale_date and amount from the daily_sales table. Do not add the LAG function yet.

SQL

SELECT sale_date, amount
FROM daily_sales
-- Your code here

Need a hint?

Use SELECT to choose columns and FROM to specify the table.

3

Add the LAG function to get previous day's sales

Modify the SELECT statement to include LAG(amount) OVER (ORDER BY sale_date) as previous_amount to get the previous row's sales amount.

SQL

SELECT sale_date, amount, LAG(amount) OVER (ORDER BY sale_date) AS previous_amount
FROM daily_sales
-- Your code here

Need a hint?

Use LAG(column) OVER (ORDER BY column) to access the previous row's value.

4

Order the results by sale_date

Add an ORDER BY sale_date ASC clause to the query to sort the results by date in ascending order.

SQL

SELECT sale_date, amount, LAG(amount) OVER (ORDER BY sale_date) AS previous_amount
FROM daily_sales
ORDER BY sale_date ASC
-- Your code here

Need a hint?

Use ORDER BY sale_date ASC to sort the output by date from earliest to latest.