Search⌘ K
AI Features
Home
Courses
Practical Data Analysis with SQL

Next and Previous Rows

Explore how to use SQL window functions LAG and LEAD to compare values in previous and next rows within grouped data. Understand how to calculate differences between rows, handle missing values with defaults, and apply custom row offsets to enhance time series analysis.

We'll cover the following...

Overview

In many types of analysis, it is often necessary to compare a row to the next or previous rows. For example, calculating the change in a stock price, calculating the increase or decrease of a certain measure, and so on. Window functions are a great fit for this type of analysis.

Given our temperature data set, say we want to find the temperature change on each day. To do that, for each day, we need to find the temperature of the previous day:

PostgreSQL
SELECT
  t_outer.*,
  (
    -- Find the last temperature in the same city
    SELECT 
      temperature
    FROM
      temperatures t_inner  
    WHERE
      -- Same city
      t_inner.city = t_outer.city
      -- Days prior to "outer" current date
      AND t_inner.day < t_outer.day
    ORDER BY
      t_inner.day DESC
    LIMIT 
      1
  ) previous_temperature
FROM
  temperatures t_outer
ORDER BY
  t_outer.city,
  t_outer.day;

The query uses a subquery to find the previous temperature in each city on each day. The subquery finds all the temperatures in the same city in days prior to the current row in the outer query. It then sorts the results by the date in descending order and takes the first result. This is how we ...