Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Using LAG() And LEAD()

Explore how to apply SQL LAG and LEAD window functions to retrieve previous and next row values in a dataset. Understand their syntax and usage in SELECT statements with filtering and ordering. Gain insight into alternate methods such as CTEs, LEFT JOINs, and subqueries for similar results. This lesson helps you write and interpret queries for comparing sales data across periods, enhancing your analytical SQL skills.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to use LAG() and LEAD() functions
SELECT   SalesID, Month, SalesAmount,
         LAG(SalesAmount, 1) OVER (ORDER BY MONTHNAME(Month)) AS PreviousMonthSales,
         LEAD(SalesAmount, 1) OVER (ORDER BY MONTHNAME(Month)) AS NextMonthSales
FROM     Sales
WHERE    CategoryID = 1
ORDER BY MONTHNAME(MONTH) DESC, SalesID ASC;

Code explanation

The explanation of the solution code is given below:

  • Lines 2–4: The SELECT statement selects the columns SalesID, Month, and SalesAmount. The LAG() function gets the sales from the previous month and labels it as PreviousMonthSales and the LEAD() function gets the sales from the next month and labels it as NextMonthSales.

  • Line 5: The FROM clause specifies the table name as Sales.

  • Line 6: The WHERE clause specifies the condition on which we filter the results to include only records with CategoryID equal to 1.

  • Line 7: The ORDER BY clause sorts the results by the MONTH column in descending order and the SalesID column in ascending order.

Recalling relevant concepts

We have covered the ...