Window Functions for Time Data: LEAD(), LAG()

Now our task is to take it a step further and learn how to use window functions to analyze time data. Ready? Hope you grabbed a cup of coffee, 'cause this is gonna be fun.

So, as always, let's answer the main question first: why do we need window functions (LEAD(), LAG())? Imagine you're working with time data, whether it's event logs, work hours, time series, or anything where the order of events matters.

For example, you might want to:

• Find out when the next event happened after the current one.
• Calculate the time difference between the current event and the previous one.
• Sort your data and figure out the difference between records.

This is where two awesome functions come in: LEAD() and LAG(). They let you grab data from the previous or next row within a certain window. It's like having a magic book where you can peek at the next page without flipping the current one.

LEAD() and LAG(): Syntax and Main Ideas

Both functions use similar syntax:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — the column you want to pull data from.
• offset (optional) — how many rows away from the current one. By default, it's 1.
• default_value (optional) — the value returned if there's no row at the given offset (like when you're at the last row).
• OVER() — this is where you set the "window" for the calculation. Usually it's ORDER BY, sometimes PARTITION BY is used to split data into groups.

Example: Simple LEAD() and LAG()

Let's make a simple events table for our experiments:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Event A', '2023-10-01 10:00:00'),
    ('Event B', '2023-10-01 11:00:00'),
    ('Event C', '2023-10-01 12:00:00'),
    ('Event D', '2023-10-01 13:00:00');

Now we want to see when the previous and next events happened for each event:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS previous_event,
    LEAD(event_date) OVER (ORDER BY event_date) AS next_event
FROM events;

The result will look like this:

Here, LAG() grabs data from the previous row, and LEAD() — from the next one. The first event has nothing to look back at, and the last one has no one to look ahead to, so they get NULL.

Example: Difference Between Events

Sometimes you need to know how much time passed between events. For that, you can just subtract one time from another:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS time_since_last_event
FROM events;

Result:

Example: Using PARTITION BY

Let's say we have several users, each with their own events. We want to find the difference between events for each user.

Let's update the table and add a user_id column:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

Now we have two users. Let's use PARTITION BY to calculate inside each group:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS time_since_last_event
FROM events;

Result:

Real-World Use Cases

1. Event logs: analyzing time between events, like user login and logout.
2. Time tracking: calculating time spent on certain tasks.
3. Behavior analytics: analyzing the sequence of customer actions in an online store.
4. Cumulative metrics calculation: using window functions to work with time series.

Common Mistakes

When working with LEAD() and LAG(), the main issues can be:

• Forgetting ORDER BY in OVER(). Without it, the function can't figure out the row order.
• Problems with time intervals or data types (TIMESTAMP vs DATE).
• Ignoring NULL values that can show up at the start and end of the window range.

To avoid these mistakes, always check your data and make sure you set the right window for your operations.

2

Task

SQL SELF, level 32, lesson 3

Locked

Extracting the Next and Previous Event

Extracting the Next and Previous Event

Open