Reset Row Number within a Window in SQL

In this blog I will show you how to reset the row number within a partition window when using the ROW_NUMBER() function in SQL.

The following is a sample dataset showing customer subscriptions with start/end dates, the RenewedSubscriptionID represents the subscription that was renewed to start a new subscription, for example SubscriptionID 101 was renewed to create SubscriptionID 102.

The goal is to identify the sequence of renewals by customer. Here we can see the result of the ROW_NUMBER() function over the CustomerID in the column called SubscriptionSequenceNumber, this represents the sequence of subscriptions by customer in date order.

So the ROW_NUMBER() function is useful for sequencing data, but we want to restart the sequence when a condition is met (i.e. there is no renewal) so the final output should look like this:

Below is the full code with the output showing each step, the steps required are:

1. CTE CustomerSubscription – get a sequence of subscriptions by customer. This is a straightforward ROW_NUMBER() over CustomerID with the output shown in the column SubscriptionSequenceNumber
2. CTE SubscriptionGroupStart – identify the first subscription in a series of subscriptions that wasn’t a renewal. This is done using the column RenewedSubscriptionID and setting GroupStartRow to NULL for any renewals, otherwise using SubscriptionSequenceNumber
3. CTE SubscriptionGroup – create groups of subscriptions by customer using MAX() on GroupStartRow over CustomerID
4. Final output – identify the sequence of subscriptions within groups using ROW_NUMBER() over CustomerID and SubscriptionGroup

The output in the column RenewalSequenceNumber shows the order of subscriptions by customer and is split into groups of renewals. This can be taken a step further to get the first SubscriptionID within a group for every subscription:

If you’re using another method to achieve a similar result, then drop a comment and share your approach.

Tags: SQL