Rank customer Transactions per segments in SQL Server-CodePudding

I have below table which has customer's transaction details.

Tranactaction date	CustomerID
1/27/2022	1
1/29/2022	1
2/27/2022	1
3/27/2022	1
3/29/2022	1
3/31/2022	1
4/2/2022	1
4/4/2022	1
4/6/2022	1

In this table consecutive transactions occurred in every two days considered as a segment. For example, Transactions between Jan 27th and Jan 29th considered as segment 1 & Transactions between Mar 29th and Apr 6th considered as Segment 2. I need to rank the transactions per segment with date order. If a transaction not fall under any segment by default the rank is 1. Expected output is below.

Segment Rank	Tranactaction date	CustomerID
1	1/27/2022	1
2	1/29/2022	1
1	2/27/2022	1
1	3/27/2022	1
2	3/29/2022	1
3	3/31/2022	1
4	4/2/2022	1
5	4/4/2022	1
6	4/6/2022	1

Can somebody guide how to achieve this in T-sql?

CodePudding user response：

Using lag() to check for change in TransDate that is within 2 days and groups together (as a segment). After that use row_number() to generate the required sequence

with 
cte as
(
    select *,
           g = case when datediff(day,
                                  lag(t.TransDate) over (order by t.TransDate),
                                  t.TransDate
                                  ) <= 2
                    then 0
                    else 1
                    end
    from   tbl t
),
cte2 as
(
    select *, grp = sum(g) over (order by TransDate)
    from cte
)
select *, row_number() over (partition by grp order by TransDate)
from   cte2

db<>fiddle demo