SQL SERIES #4: LEAD() Window Function

Problem Statement

Given a table sales_data that logs daily sales figures for multiple stores, the goal is to calculate the percentage change in sales from one day to the next for each store.

Input

The table sales_data has the following structure:

• store_id (integer): Identifier for the store
• sale_date (date): The date of the sale
• sales_amount (decimal): The amount of sales for that day

Code

Code Explanation:

The LEAD() function in SQL is used to access data from the subsequent row within the same result set. It retrieves values from a following row based on a specified offset, allowing for comparisons between current and future rows. In the query above, LEAD(sales_amount, 1) fetches the sales amount for the next day to compute the percentage change in sales from one day to the next.

The WITH sales_comparison AS clause creates a Common Table Expression (CTE) to prepare the data. The LEAD(sales_amount, 1) function retrieves the sales amount for the next day within the same store, using PARTITION BY store_id and ORDER BY sale_date for proper sequencing. The final SELECT statement includes the original sales data and computes the percentage change from the current day to the next day using a CASE statement

Output