LAG and LEAD for repeat-purchase intervals

LAG retrieves a value from an earlier row in a window's ordering; LEAD retrieves one from a later row. Partitioning by customer and ordering by purchase time lets you compare each purchase with the previous or next observed purchase.

The word “observed” matters. A customer's first row in your dataset may not be their first-ever purchase, and a final row with no next purchase does not mean the customer will never return.

The advanced SQL lab contains 11 synthetic purchases by five customers across January–April 2026. Its observation cutoff is 1 May. We will calculate gaps and then examine the limits of their interpretation.

Build the previous-purchase column

WITH ordered AS (
    SELECT purchase_id, customer_id, purchased_at,
        LAG(purchased_at) OVER (
            PARTITION BY customer_id
            ORDER BY purchased_at, purchase_id
        ) AS previous_purchase
    FROM purchases
)
SELECT purchase_id, customer_id, purchased_at, previous_purchase,
       julianday(purchased_at) - julianday(previous_purchase)
           AS days_since_previous
FROM ordered
ORDER BY customer_id, purchased_at, purchase_id;

The first observed purchase for each customer has a NULL gap. Later gaps are A: 31 and 55 days; B: 18 and 44; C: 28; D: 31. E has no observed repeat purchase.

The fixture stores dates rather than timestamps, so the differences represent elapsed calendar-date distances in its single-timezone convention. For timestamped data, specify whether you need elapsed hours, calendar days or business days. Those are not interchangeable around time boundaries.

purchase_id breaks ties reproducibly. If two purchases occur at the same timestamp, the business may instead consider them one checkout or two separate orders. Decide the event identity before measuring gaps.

Use LEAD to inspect the next observed event

SELECT customer_id, purchase_id, purchased_at,
       LEAD(purchased_at) OVER (
           PARTITION BY customer_id
           ORDER BY purchased_at, purchase_id
       ) AS next_observed_purchase
FROM purchases
ORDER BY customer_id, purchased_at, purchase_id;

This is useful for describing intervals or preparing a follow-up analysis. It is not a feature you can give a model at purchase time: the next purchase is future information.

Likewise, a “days until next purchase” field can be a label for a carefully designed prediction problem, but using it as an input would leak the answer. The same SQL operation can be valid for retrospective analysis and invalid for prediction-time feature construction.

For function semantics, see SQLite's LAG and LEAD documentation.

Do not filter away the history you need

If you filter purchases to March before applying LAG, a February purchase is no longer available as the previous event. The resulting NULL means “no earlier row in this filtered input,” not “no previous purchase.”

To display March gaps with earlier history retained, calculate the window over the required history in a CTE and filter the outer result to March. If access or storage limits prevent full history, label the analysis as first observed within the available window.

A source cutoff is also necessary for reproducibility. Late-arriving purchases can insert a new event between two previously adjacent rows and change both gaps.

Explain the average's selection bias

The six observed repeat intervals in this fixture average 34.5 days. That number describes intervals that actually ended in another observed purchase. It excludes E and every customer's still-open interval after their last purchase.

Calling it “the time the average customer takes to return” would overstate the result. Customers who have not returned have incomplete follow-up, and newer customers have had less time to do so.

You can report the observed-gap distribution with its scope clearly stated. A time-to-event analysis that handles censoring is a different method and needs explicit assumptions. A simple SQL average is not a substitute for that design.

Verify the intervals before interpreting them

Check for duplicated purchase IDs, negative gaps, unexpected zero gaps and inconsistent timezone handling. Confirm that customer identity is stable; an account merge can make unrelated histories appear consecutive.

Inspect at least one customer manually. A's sequence is January 5, February 5 and April 1, producing 31 and 55 days. That small check makes it easier to catch a reversed subtraction or an incorrect partition.

If the output will trigger marketing actions, add business review and consent requirements to that workflow separately. This article demonstrates an analytical calculation, not authorization to contact anyone.

Exercise: calculate the March purchases' gaps using two approaches: filtering before LAG and filtering afterward. Explain why C's February 2 purchase must remain available to obtain its 28-day March gap.

NeuraPath's Data Analytics with Generative AI programme covers SQL and statistical interpretation. Repeat-purchase analysis is a useful example of why both are needed: the query can calculate a gap correctly while the narrative still makes an unsupported claim about customer behaviour.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL running totals: choose the correct window frame.
• Continue with Build a monthly customer cohort table in SQL.