Add rownum based on status column

Question

I have a table having columns id, time, and status. I want to add rownum between every CANCELLED status and CLOSED status. So every CLOSED status entry should contain the count of the total occurrence of CANCELLED immediately before it. The final output should look like this

| id               | time                            | status    | rownum |
| :--------------- | :------------------------------ | :-------- | :----  |
| 5069640_17a678bf | 2023-07-06 06:08:50.5209 +00:00 | CANCELLED | 1      |
| 5069640_17a678bf | 2023-07-06 06:51:37.8891 +00:00 | CLOSED    | 1      |
| 5069640_17a678bf | 2023-07-06 06:52:19.7796 +00:00 | CANCELLED | 1      |
| 5069640_17a678bf | 2023-07-06 06:54:13.3729 +00:00 | CLOSED    | 1      |
| 5069640_17a678bf | 2023-07-06 06:54:49.5915 +00:00 | CANCELLED | 1      |
| 5069640_17a678bf | 2023-07-06 06:55:57.6069 +00:00 | CANCELLED | 2      |
| 5069640_17a678bf | 2023-07-06 07:18:07.9313 +00:00 | CANCELLED | 3      |
| 5069640_17a678bf | 2023-07-06 07:46:09.6142 +00:00 | CLOSED    | 3      |
| 5069640_17a678bf | 2023-07-06 07:54:32.5347 +00:00 | CLOSED    | 0      |
| 5069640_17a678bf | 2023-07-06 07:55:58.4408 +00:00 | CLOSED    | 0      |
| 5069640_17a678bf | 2023-07-06 08:06:02.2827 +00:00 | CANCELLED | 1      |
| 5069640_17a678bf | 2023-07-06 08:09:23.001 +00:00  | CANCELLED | 2      |
| 5069640_17a678bf | 2023-07-06 08:13:08.2036 +00:00 | CLOSED    | 2      |
| 5069640_17a678bf | 2023-07-06 08:43:31.1047 +00:00 | CANCELLED | 1      |
| 5069640_17a678bf | 2023-07-06 10:04:46.8257 +00:00 | ACTIVE    | 1      |

I have used the below query to achieve until a point where I see this output table. However I need to fix the rownum as per the requirement described above

WITH CTE AS (
    SELECT
        v.BasketId,
        v.StoreId,
        x.person_id,
        v.VBasket,
        v.CreatedAt,
        FORMAT(v.CreatedAt, 'yyyy-MM-dd') AS CreatedDate,
        v.Status,
        ROW_NUMBER() OVER (PARTITION BY x.person_id, v.Status ORDER BY v.CreatedAt) AS rn
    FROM
        [Basket] v
    CROSS APPLY
        OPENJSON(v.TBasket)
        WITH (
            data NVARCHAR(MAX) AS JSON
        ) j1
    CROSS APPLY
        OPENJSON(j1.data, '$.person_ids')
        WITH (
            person_id NVARCHAR(MAX) '$'
        ) x
    WHERE
        x.person_id = '5069640_17a678bf'
),
CTE2 AS (
    SELECT
        BasketId,
        StoreId,
        person_id,
        VBasket,
        CreatedAt,
        CreatedDate,
        Status,
        CASE
            WHEN Status = 'CANCELLED' THEN
                ROW_NUMBER() OVER (PARTITION BY person_id, Status ORDER BY CreatedAt)
            ELSE
                1
        END AS rownum
    FROM CTE
)
SELECT
    person_id,
    CreatedAt ,
    Status,
    rownum
FROM CTE2
ORDER BY CreatedAt;

| id               | time                            | status    | rownum |
| :--------------- | :------------------------------ | :-------- | :----  |
| 5069640_17a678bf | 2023-07-06 06:08:50.5209 +00:00 | CANCELLED | 1      |
| 5069640_17a678bf | 2023-07-06 06:51:37.8891 +00:00 | CLOSED    | 1      |
| 5069640_17a678bf | 2023-07-06 06:52:19.7796 +00:00 | CANCELLED | 2      |
| 5069640_17a678bf | 2023-07-06 06:54:13.3729 +00:00 | CLOSED    | 1      |
| 5069640_17a678bf | 2023-07-06 06:54:49.5915 +00:00 | CANCELLED | 3      |
| 5069640_17a678bf | 2023-07-06 06:55:57.6069 +00:00 | CANCELLED | 4      |
| 5069640_17a678bf | 2023-07-06 07:18:07.9313 +00:00 | CANCELLED | 5      |
| 5069640_17a678bf | 2023-07-06 07:46:09.6142 +00:00 | CLOSED    | 1      |
| 5069640_17a678bf | 2023-07-06 07:54:32.5347 +00:00 | CLOSED    | 1      |
| 5069640_17a678bf | 2023-07-06 07:55:58.4408 +00:00 | CLOSED    | 1      |
| 5069640_17a678bf | 2023-07-06 08:06:02.2827 +00:00 | CANCELLED | 6      |
| 5069640_17a678bf | 2023-07-06 08:09:23.001 +00:00  | CANCELLED | 7      |
| 5069640_17a678bf | 2023-07-06 08:13:08.2036 +00:00 | CLOSED    | 1      |
| 5069640_17a678bf | 2023-07-06 08:43:31.1047 +00:00 | CANCELLED | 8      |
| 5069640_17a678bf | 2023-07-06 10:04:46.8257 +00:00 | ACTIVE    | 1      |

Update 1: As per Matts's comment I get the below results

| id               | time                            | status    | rownum |
| :--------------- | :------------------------------ | :-------- | :----  |
| 5069640_17a678bf |2023-07-06 06:08:50.5209 +00:00  |  CANCELLED| 1      |
| 5069640_17a678bf |2023-07-06 06:51:37.8891 +00:00  |  CLOSED   | 1      |
| 5069640_17a678bf |2023-07-06 06:52:19.7796 +00:00  |  CANCELLED| 3      |
| 5069640_17a678bf |2023-07-06 06:54:13.3729 +00:00  |  CLOSED   | 3      |
| 5069640_17a678bf |2023-07-06 06:54:49.5915 +00:00  |  CANCELLED| 6      |
| 5069640_17a678bf |2023-07-06 06:55:57.6069 +00:00  |  CANCELLED| 10     |
| 5069640_17a678bf |2023-07-06 07:18:07.9313 +00:00  |  CANCELLED| 15     |
| 5069640_17a678bf |2023-07-06 07:46:09.6142 +00:00  |  CLOSED   | 15     |
| 5069640_17a678bf |2023-07-06 07:54:32.5347 +00:00  |  CLOSED   | 15     |
| 5069640_17a678bf |2023-07-06 07:55:58.4408 +00:00  |  CLOSED   | 15     |
| 5069640_17a678bf |2023-07-06 08:06:02.2827 +00:00  |  CANCELLED| 21     |
| 5069640_17a678bf |2023-07-06 08:09:23.001 +00:00   |  CANCELLED| 28     |
| 5069640_17a678bf |2023-07-06 08:13:08.2036 +00:00  |  CLOSED   | 28     |
| 5069640_17a678bf |2023-07-06 08:43:31.1047 +00:00  |  CANCELLED| 36     |
| 5069640_17a678bf |2023-07-06 10:04:46.8257 +00:00  |  ACTIVE   | 36     |

Answer 1

WITH CTE AS (
    SELECT
        v.BasketId,
        v.StoreId,
        x.person_id,
        v.VBasket,
        v.CreatedAt,
        FORMAT(v.CreatedAt, 'yyyy-MM-dd') AS CreatedDate,
        v.Status,
        ROW_NUMBER() OVER (PARTITION BY x.person_id, v.Status ORDER BY v.CreatedAt) AS rn
    FROM
        [Basket] v
    CROSS APPLY
        OPENJSON(v.TBasket)
        WITH (
            data NVARCHAR(MAX) AS JSON
        ) j1
    CROSS APPLY
        OPENJSON(j1.data, '$.person_ids')
        WITH (
            person_id NVARCHAR(MAX) '$'
        ) x
    WHERE
        x.person_id = '5069640_17a678bf'
),
CTE2 AS (
    SELECT
        BasketId,
        StoreId,
        person_id,
        VBasket,
        CreatedAt,
        CreatedDate,
        Status,
        CASE
            WHEN Status = 'CANCELLED' THEN
                ROW_NUMBER() OVER (PARTITION BY person_id, Status ORDER BY CreatedAt)
            ELSE
                0
        END AS rownum
    FROM CTE
),
CTE3 AS (
    SELECT
        BasketId,
        StoreId,
        person_id,
        VBasket,
        CreatedAt,
        CreatedDate,
        Status,
        SUM(rownum) OVER (PARTITION BY person_id ORDER BY CreatedAt) AS running_rownum
    FROM CTE2
)
SELECT
    person_id,
    CreatedAt,
    Status,
    CASE
        WHEN Status = 'CLOSED' THEN
            LAG(running_rownum) OVER (PARTITION BY person_id ORDER BY CreatedAt)
        ELSE
            running_rownum
    END AS rownum
FROM CTE3
ORDER BY CreatedAt;