SQL Academy · Lesson

LAG, LEAD and Time-Series Patterns

Compare each row to its previous or next sibling with LAG and LEAD, and build period-over-period deltas.

LAG, LEAD and Time-Series Patterns is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

LAG and LEAD: Look Backwards / Forwards

These access neighbouring rows in the window:

LAG(col, n, default) — value n rows before the current row
LEAD(col, n, default) — value n rows after

Previous Row

Compare each order to the previous one for the same user:

SELECT id, user_id, created_at, total,
       LAG(total) OVER (
         PARTITION BY user_id ORDER BY created_at
       ) AS prev_total
FROM orders;

Day-Over-Day Delta

Difference from the previous day:

SELECT day, revenue,
       revenue - LAG(revenue) OVER (ORDER BY day) AS delta
FROM daily_revenue
ORDER BY day;

Year-Over-Year

LAG with offset 12 on a monthly series:

SELECT month, revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year_revenue,
       revenue / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) - 1 AS yoy
FROM monthly_revenue;

Detecting Gaps

Find days with missing data:

WITH d AS (
  SELECT day, LAG(day) OVER (ORDER BY day) AS prev_day FROM daily_signups
)
SELECT day, prev_day, day - prev_day AS gap_days
FROM d
WHERE day - prev_day > 1;

Sessionisation

Group rows into sessions when the gap exceeds a threshold:

WITH gaps AS (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
                 > INTERVAL '30 min'
              THEN 1 ELSE 0 END AS new_session
  FROM events
)
SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps;

First and Last in Group

FIRST_VALUE and LAST_VALUE retrieve the bounds of the window:

SELECT id, total,
       FIRST_VALUE(total) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order,
       LAST_VALUE(total)  OVER (PARTITION BY user_id ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order
FROM orders;
-- LAST_VALUE needs the explicit frame, otherwise default frame ends at current row.

NTH_VALUE

Pick the Nth value in the partition:

SELECT id,
       NTH_VALUE(total, 2) OVER (
         PARTITION BY user_id ORDER BY total DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_largest
FROM orders;

Time-Series Default Value

The third argument of LAG is a default when no previous row exists:

SELECT day, revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM daily_revenue;
-- delta on first day = revenue itself (vs NULL)

Self-Join Equivalent

You could write LAG as a self-join, but it would be slower and uglier. Always prefer window functions.

Performance

Window functions traverse the partition once. The bottleneck is sorting — index the ORDER BY columns where possible.

Recap

LAG and LEAD enable row-to-row comparison.

Previous/next within the partition
Deltas, period-over-period, sessionisation
FIRST_VALUE/LAST_VALUE for bounds

Quick Check

You want to compute day-over-day revenue change. Which window function?

Frequently asked questions

Is the “LAG, LEAD and Time-Series Patterns” lesson free?

Yes — the full text of “LAG, LEAD and Time-Series Patterns” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “LAG, LEAD and Time-Series Patterns”?

Compare each row to its previous or next sibling with LAG and LEAD, and build period-over-period deltas. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LAG, LEAD and Time-Series Patterns” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy