break row_number() sequence based on flag variable

Question

Hoping to find something.

I have data as shown below

id  month flag
111 jan   1
111 feb   1
111 mar   1
111 apr   0
111 may   0
111 jun   1
222 jan   1
222 feb   1
222 mar   0
222 apr   0
222 may   0
222 jun   1

I looking for the output as below

id  month flag order
111 jan   1    1
111 feb   1    2
111 mar   1    3
111 apr   0    1
111 may   0    2
111 jun   1    1
222 jan   1    1
222 feb   1    2
222 mar   0    1
222 apr   0    2
222 may   0    3
222 jun   1    1

I tried row_number() but the problem is we cannot break the sequence and start over. At an overall level, when ever there is a change in flag variable from 0 to 1 or 1 to 0 I need to start counting from 1 for each id separately

Answer 1

Assuming SQL Server, here is an example:

DECLARE @T table (id int, [month] char(3), flag bit)

INSERT INTO @T
VALUES
(111, 'jan', 1)
,(111, 'feb', 1)
,(111, 'mar', 1)
,(111, 'apr', 0)
,(111, 'may', 0)
,(111, 'jun', 1)
,(222, 'jan', 1)
,(222, 'feb', 1)
,(222, 'mar', 0)
,(222, 'apr', 0)
,(222, 'may', 0)
,(222, 'jun', 1)

SELECT
    id
    , [month]
    , flag
    , ROW_NUMBER() OVER (PARTITION BY id, section ORDER BY monthNum) [order]
FROM
    (
        SELECT
            id
            , [month]
            , monthNum
            , flag
            , SUM(CASE WHEN newValue = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY monthNum) section
        FROM
            (
                SELECT
                    id
                    , [month]
                    , monthNum
                    , flag
                    , CASE WHEN LAG(flag, 1, ABS(flag - 1)) OVER (PARTITION BY id ORDER BY monthNum) = flag THEN 0 ELSE 1 END newValue
                FROM
                    (
                        SELECT
                            id
                            , [month]
                            , MONTH(CAST('1 ' + [month] + ' 17' AS datetime)) monthNum
                            , flag
                        FROM @T
                    ) Q
            ) Q2
    ) Q3