Search⌘ K
Home/
Courses/
SQL Interview Preparation—Intermediate Level

Answer: Using LAG() And LEAD()

Find a detailed explanation of using LAG() and LEAD() functions.

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 following concepts in this question:

  • Selective columns

  • Aliases ...