Learn Analytic Functions: LEAD, LAG, FIRST_VALUE, LAST_VALUE

Analytic window functions allow you to look beyond the current row and access data from other rows within the same window frame. Four of the most useful analytic functions are LEAD, LAG, FIRST_VALUE, and LAST_VALUE. These functions are essential for trend analysis, comparisons, and understanding changes over time.

LEAD: retrieves a value from a following row within the window;
LAG: retrieves a value from a preceding row within the window;
FIRST_VALUE: returns the value of the first row in the window frame;
LAST_VALUE: returns the value of the last row in the window frame.

By using these functions, you can compare current values to previous or future ones, or identify the earliest or latest values within a group. This is especially powerful for tracking trends, such as employee performance changes or salary progression.


              12345678910
            
SELECT
  employee_id,
  review_date,
  score,
  LAG(score) OVER (
    PARTITION BY employee_id
    ORDER BY review_date
  ) AS previous_score
FROM performance
ORDER BY employee_id, review_date;

In business scenarios, analytic functions help you spot patterns and changes. For instance, by using LAG, you can track whether an employee's performance score has improved or declined compared to the previous review. Similarly, LEAD allows you to see the next value, which is useful for forecasting or identifying upcoming changes. FIRST_VALUE and LAST_VALUE are valuable in cases such as finding the starting salary for each department or determining the most recent salary in a group. These insights can drive HR decisions, compensation planning, and performance management.


              1234567891011
            
SELECT
  department,
  name,
  hire_date,
  salary,
  FIRST_VALUE(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
  ) AS department_earliest_salary
FROM employees
ORDER BY department, hire_date;

1. Which function retrieves the value from the previous row in the window?

2. What is a typical use case for FIRST_VALUE in HR analytics?

3. Fill in the blank to get the next score after each review date:

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 5

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain how the LEAD function works with an example?

What is the difference between LAG and FIRST_VALUE?

How can I use these analytic functions for salary progression analysis?

Swipe to show menu

LEAD: retrieves a value from a following row within the window;
LAG: retrieves a value from a preceding row within the window;
FIRST_VALUE: returns the value of the first row in the window frame;
LAST_VALUE: returns the value of the last row in the window frame.