MySQL Query to find Lead and Lag Values for Sales

Find the Lead and Lag Values for Sales

Write a MySQL query to find the lead and lag values for sales using a window function.

Note:

The LEAD() and LAG() functions in MySQL are used to access the next (LEAD) or previous (LAG) row's value within a result set, based on a specified order. For sales data, LAG(sales) retrieves the sales value from a prior row (e.g., previous month), while LEAD(sales) fetches the sales value from a subsequent row (e.g., next month). These functions are useful for comparing current sales to past or future performance without collapsing rows. For example, you can calculate the difference between current and previous month's sales using LAG().

Solution:

-- Begin the SELECT statement to define the columns to retrieve from the MonthlySales table
SELECT 
    -- Retrieve the Month column to display the corresponding month for each sales record
    Month, 
    -- Retrieve the Sales column to show the sales amount for each month
    Sales,
    -- Retrieve the sales value from the previous month using the LAG function:
    -- LAG(Sales, 1) gets the Sales value from one row before the current row.
    -- ORDER BY Month ensures that the calculation follows chronological order.
    LAG(Sales, 1) OVER (ORDER BY Month) AS PreviousMonthSales,
    -- Retrieve the sales value from the next month using the LEAD function:
    -- LEAD(Sales, 1) gets the Sales value from one row after the current row.
    -- ORDER BY Month ensures that the calculation follows chronological order.
    LEAD(Sales, 1) OVER (ORDER BY Month) AS NextMonthSales
-- Specify the MonthlySales table as the source of the data
FROM MonthlySales;

Explanation:

• Purpose of the Query:
• The goal is to retrieve the sales value from the previous month and the next month for each row.
• This demonstrates the use of the LAG() and LEAD() window functions.
• Key Components:
• LAG(Sales, 1) OVER (ORDER BY Month): Retrieves the sales value from the previous month.
• LEAD(Sales, 1) OVER (ORDER BY Month): Retrieves the sales value from the next month.
• Why use Window Functions?:
• Window functions allow you to access data from adjacent rows, making it easier to compare values over time.
• Real-World Application:
• For example, in a retail business, you might want to compare monthly sales to the previous and next month’s to analyze trends or seasonality.

For more Practice: Solve these Related Problems:

• Write a MySQL query to compute the difference in sales between the current and next month.
• Write a MySQL query to find the sales growth percentage from one month to the next.
• Write a MySQL query to compute a running total of sales per month.
• Write a MySQL query to compare sales in the current month with the same month in the previous year.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Calculate the Percentile Rank of Each Employee's Salary.
Next MySQL Exercise: Calculate the Difference between Current and Next Salary.