SQL · Window Functions

LAG and LEAD

Mediumlagleadwindow

Interview question

What do LAG and LEAD do?

How to answer

The short answer

LAG() gets a value from a previous row.

LEAD() gets a value from a future row.

Example:

SQL
LAG(event_time) OVER (
    ORDER BY event_time
)

gets the previous event time.

Then:

SQL
event_time - LAG(event_time) OVER (...)

can calculate the time between events.

LEAD() is useful when comparing the current event with the next event.

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill