First and Repeat Purchases: Tagging Customer Orders With ROW_NUMBER

A growing order total does not tell you whether customers returned. Tagging repeat purchases by each customer's order sequence separates a first sale from the relationship that follows it.

Decide What Counts as an Order

A customer can appear in several order rows, several line items, and several payment records. Those are different grains. Start with one row per qualifying order before numbering purchases, or one large basket starts looking like several visits.

Define the qualifying population first. Decide how cancellations, unpaid orders, returns, and test accounts are treated. Keep that rule consistent across first-order dates, second-order dates, and the cohort summary.

I check the source grain before reviewing the retention calculation. An extra join can turn a first order into an apparent repeat purchase. The window function will number those rows politely without asking what they represent.

Use the complete relevant purchase history when finding the first order. Filtering to this month's orders before numbering would call an established customer's next order a first purchase. Apply the reporting cohort filter after identifying the genuine first order.

The following examples require SQL Server 2012 or later for LEAD and DATEFROMPARTS. The sample values illustrate ordering rules, not an observed customer population. Replace the source with your approved order-level data.

Give Same-Day Orders a Stable Sequence

A date or timestamp alone does not always identify order sequence. Several orders can share one timestamp, especially when an import assigns it to a whole batch. Add a stable unique identifier to the window ordering.

CREATE TABLE #CustomerOrders
(
    OrderID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderAt datetime2(0) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #CustomerOrders
VALUES (1, 10, '2026-01-03T10:00:00', 25),
       (2, 10, '2026-01-03T10:00:00', 40),
       (3, 10, '2026-02-05T12:00:00', 15),
       (4, 20, '2026-01-10T09:00:00', 80),
       (5, 30, '2026-02-02T08:00:00', 35),
       (6, 30, '2026-02-20T14:00:00', 50);
SELECT OrderID, CustomerID, OrderAt, Amount,
       ROW_NUMBER() OVER
       (PARTITION BY CustomerID ORDER BY OrderAt, OrderID) AS PurchaseNumber
FROM #CustomerOrders
ORDER BY CustomerID, OrderAt, OrderID;

OrderID supplies deterministic ordering for tied timestamps in this example. It does not establish which physical checkout happened first if the source never recorded that fact. Deterministic reporting and complete historical evidence are separate properties. Run the later blocks in the same query window, because the temporary table lives only in that session.

Use the same ordering everywhere you calculate the sequence. A different tie breaker in the second-order query creates disagreement with the purchase-number report. Keep the rule visible in the code rather than relying on incidental plan order.

Label First Orders and Repeat Purchases

Put the sequence in a CTE, then derive a readable label from it. PurchaseNumber 1 identifies the first qualifying order. Any later number identifies another qualifying purchase by the same customer.

WITH Numbered AS
(
    SELECT *, ROW_NUMBER() OVER
    (PARTITION BY CustomerID ORDER BY OrderAt, OrderID) AS PurchaseNumber
    FROM #CustomerOrders
)
SELECT OrderID, CustomerID, OrderAt, Amount, PurchaseNumber,
       CASE WHEN PurchaseNumber = 1 THEN N'First' ELSE N'Repeat' END AS PurchaseKind
FROM Numbered
ORDER BY CustomerID, PurchaseNumber;

These labels describe orders, not customers. A customer with several repeats contributes several repeat-order rows. Counting those rows is different from counting customers who returned at least once.

Keep both measures when the business needs them. Repeat-order volume describes activity after acquisition. Returning-customer count describes how many first-time customers established another purchase. Neither should quietly replace the other in a report.

If you filter labeled orders to a recent sales window, retain the earlier history used to assign the labels. A first purchase before the window still makes an order inside the window a repeat.

Read the Second Order From the First Row

LEAD retrieves the next ordered value in the customer's partition. On the first row, that next order is the second qualifying purchase. Use the same timestamp and identifier ordering as ROW_NUMBER.

WITH Sequenced AS
(
    SELECT CustomerID, OrderID, OrderAt,
           ROW_NUMBER() OVER
           (PARTITION BY CustomerID ORDER BY OrderAt, OrderID) AS PurchaseNumber,
           LEAD(OrderAt) OVER
           (PARTITION BY CustomerID ORDER BY OrderAt, OrderID) AS NextOrderAt
    FROM #CustomerOrders
)
SELECT CustomerID, OrderAt AS FirstOrderAt,
       NextOrderAt AS SecondOrderAt,
       DATEDIFF(day, OrderAt, NextOrderAt) AS DaysToSecondOrder
FROM Sequenced
WHERE PurchaseNumber = 1
ORDER BY CustomerID;

A null second-order date means no next qualifying order exists in the available data. It does not prove the customer will never return. The observation period and completeness of the source define what you can conclude.

DATEDIFF(day) counts date boundaries. Two orders around midnight can therefore have a day difference of one without being a full day apart. Use a finer unit when elapsed hours are the intended measure.

A same-day second order produces zero day boundaries. Keep that value rather than discarding it as an error. If the business counts only separate shopping days, define and implement that different rule before numbering.

Summarize Repeat Purchases by First-Order Month

A cohort groups customers by when their qualifying relationship began. Derive the month from the first-order timestamp, then count one first-row record per customer. That avoids inflating the cohort with later purchases.

WITH Sequenced AS
(
    SELECT CustomerID, OrderAt,
           ROW_NUMBER() OVER
           (PARTITION BY CustomerID ORDER BY OrderAt, OrderID) AS PurchaseNumber,
           LEAD(OrderAt) OVER
           (PARTITION BY CustomerID ORDER BY OrderAt, OrderID) AS SecondOrderAt
    FROM #CustomerOrders
), FirstOrders AS
(
    SELECT *, DATEFROMPARTS(YEAR(OrderAt), MONTH(OrderAt), 1) AS CohortMonth
    FROM Sequenced WHERE PurchaseNumber = 1
)
SELECT CohortMonth, COUNT_BIG(*) AS CustomersAcquired,
       SUM(CASE WHEN SecondOrderAt IS NOT NULL THEN CONVERT(bigint, 1) ELSE 0 END) AS ReturningCustomers,
       AVG(CONVERT(decimal(12,2), DATEDIFF(day, OrderAt, SecondOrderAt))) AS AverageDaysToReturn
FROM FirstOrders
GROUP BY CohortMonth
ORDER BY CohortMonth;

AVG ignores null return intervals. Its denominator therefore includes returning customers, not everyone acquired. State that distinction beside the measure so it does not look like an average waiting time for the whole cohort.

For a return rate, divide returning customers by acquired customers using decimal arithmetic. Show the observation cutoff too. A recent cohort has had less opportunity to return than an older one.

Compare Cohorts With Equal Opportunity

I compare fixed observation windows when judging retention across cohorts. For example, choose an agreed number of days after the first purchase and count returns inside it. Exclude customers whose full observation window has not yet elapsed from that comparison.

What does a customer acquired yesterday have in common with one observed for a year? Their first-order labels match, but their opportunities to return do not. An unrestricted lifetime return rate makes that difference easy to overlook.

Use an index beginning with CustomerID, OrderAt, and OrderID when that ordering supports the broader workload. Check the actual plan and data volume before adopting it. The query's clean sequence still needs an efficient source.

For a fixed return window, compare SecondOrderAt with DATEADD(day, @WindowDays, FirstOrderAt). Define whether equality at the boundary counts as inside that window. Then require the first order to occur early enough that the full window ends before your observation cutoff. That gives each included customer the same opportunity to qualify.

Late-arriving orders can change a customer's apparent first purchase. Record the data cutoff and rerun affected cohorts when historical imports arrive. A corrected first date also changes the cohort month, so keep the report refresh policy explicit.

For merged customer identities, apply the approved identity mapping before numbering orders. Two accounts belonging to one customer otherwise look like separate first purchases. Preserve the original identifiers for traceability instead of rewriting the source history only to improve a retention percentage.

Repeat purchases depend on the complete customer history used to establish the first purchase. Changing that history window can change which orders count as repeat purchases.

Related reading on this blog: What’s the Difference between ROW_NUMBER, RANK, and DENSE_RANK? Notes from the Field #096 and Finding Duplicate Customers With T-SQL.

A repeat-order total is not customer retention, it is activity that needs a first purchase and a fair observation window.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.