[SQL Interview] Differences Between LEAD() and LAG()

In a data analyst interview, various questions can be asked to evaluate your data analysis and SQL query skills. This time, let’s look at a question about the differences between LEAD() and LAG() functions, which are useful for analyzing time series or sequential data.

Question: Explain the difference between LEAD() and LAG() functions and illustrate how these two functions can be used in a real business scenario.

Answer:

LEAD() Function: The LEAD() function returns the value of the next row in the dataset based on the current row. This allows you to retrieve data from the subsequent row relative to the current row.

LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)

• column_name: The column from which to retrieve the next row’s value.
• offset: The number of rows forward from the current row (default is 1).
• default_value: The value to return if the next row does not exist (optional).
• PARTITION BY partition_column: The column to partition the data by (optional).
• ORDER BY order_column: The column to order the data by (required).

LAG() Function: The LAG() function returns the value of the previous row in the dataset based on the current row. This allows you to retrieve data from the preceding row relative to the current row.

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)

• column_name: The column from which to retrieve the previous row’s value.
• offset: The number of rows backward from the current row (default is 1).
• default_value: The value to return if the previous row does not exist (optional).
• PARTITION BY partition_column: The column to partition the data by (optional).
• ORDER BY order_column: The column to order the data by (required).

These two functions are particularly useful for comparing data over time or for sequential analysis.

Example: Analyzing Daily Sales Data

A company wants to analyze its daily sales data. They have sales data for each date and want to compare sales figures with the previous and next days to understand sales trends. In this case, LAG() and LEAD() functions can be used to retrieve sales data for the previous and next days, respectively.

1. Calculating the sales change rate compared to the previous day:

SELECT 
 sale_date, 
 amount, 
 LAG(amount, 1) OVER (ORDER BY sale_date) AS previous_amount, 
 (amount - LAG(amount, 1) OVER (ORDER BY sale_date)) / LAG(amount, 1) OVER (ORDER BY sale_date) * 100 AS change_from_previous_day 
FROM dataset.sales;

2. Calculating the sales change rate compared to the next day:

SELECT sale_date, amount, LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount, (LEAD(amount, 1) OVER (ORDER BY sale_date) - amount) / amount * 100 AS change_to_next_day FROM dataset.sales;

Example Results:

In this example:

• The LAG() function retrieves the amount from the previous row, allowing you to compare the current day’s sales with the previous day’s sales.
• The LEAD() function retrieves the amount from the next row, allowing you to compare the current day’s sales with the next day’s sales.

How to Address This in an Interview

1. Understanding Basic Concepts: Clearly explain the basic definitions and differences between LEAD() and LAG() functions.
2. Explain the Syntax: Describe the syntax of each function, including the roles of offset and default_value.
3. Apply to Real Scenarios: Explain a business problem where LEAD() and LAG() can be applied, emphasizing the insights gained.
4. Provide Code Examples: Write out SQL query examples and explain the expected results.
5. Prepare for Additional Questions: Be ready to explain why LEAD() and LAG() are important and discuss the benefits and limitations of using these functions.

By following this structured approach, you can demonstrate a thorough understanding of LEAD() and LAG() functions and their practical applications in business analysis during an interview.