SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Transactions (Id int , SessionId int, TransactionType varchar(50), DateTimeEnd datetime, DateStart datetime, Rank int);
INSERT INTO Transactions (Id, SessionId, TransactionType, DateTimeEnd, DateStart, Rank)
VALUES
 (1, 1, 'Deposit',    '2017-01-20T11:16:33Z', '2017-01-20T11:16:33Z', 600),
 (2, 1, 'Withdrawal', '2017-01-21T11:16:33Z', '2017-01-20T11:16:33Z', 100),
 (3, 2, 'Deposit',    '2017-02-23T11:16:33Z', '2017-02-23T11:16:33Z', 500),
 (4, 1, 'Withdrawal', '2017-01-24T11:16:33Z', '2017-01-21T11:16:33Z', 150),
 (5, 1, 'Withdrawal', '2017-01-26T11:16:33Z', '2017-01-24T11:16:33Z', 150),
 (6, 2, 'Withdrawal', '2017-02-27T11:16:33Z', '2017-02-23T11:16:33Z', 200),
 (7, 1, 'Withdrawal', '2017-01-28T11:16:33Z', '2017-01-26T11:16:33Z', 10),
 (8, 1, 'Withdrawal', '2017-01-30T11:16:33Z', '2017-01-28T11:16:33Z', 10),
 (9, 1, 'Withdrawal', '2017-01-31T11:16:33Z', '2017-01-30T11:16:33Z', 10);
9 rows affected

select * from transactions


Id
SessionId
TransactionType
DateTimeEnd
DateStart
Rank


1
1
Deposit
2017-01-20 11:16:33.000
2017-01-20 11:16:33.000
600

2
1
Withdrawal
2017-01-21 11:16:33.000
2017-01-20 11:16:33.000
100

3
2
Deposit
2017-02-23 11:16:33.000
2017-02-23 11:16:33.000
500

4
1
Withdrawal
2017-01-24 11:16:33.000
2017-01-21 11:16:33.000
150

5
1
Withdrawal
2017-01-26 11:16:33.000
2017-01-24 11:16:33.000
150

6
2
Withdrawal
2017-02-27 11:16:33.000
2017-02-23 11:16:33.000
200

7
1
Withdrawal
2017-01-28 11:16:33.000
2017-01-26 11:16:33.000
10

8
1
Withdrawal
2017-01-30 11:16:33.000
2017-01-28 11:16:33.000
10

9
1
Withdrawal
2017-01-31 11:16:33.000
2017-01-30 11:16:33.000
10


SELECT t.*,
                         LAG(id) OVER (PARTITION BY SessionId, TransactionType ORDER BY id) as prev_st_id,
                         LAG(id) OVER (ORDER BY id) as prev_id
                  FROM Transactions t


Id
SessionId
TransactionType
DateTimeEnd
DateStart
Rank
prev_st_id
prev_id


1
1
Deposit
2017-01-20 11:16:33.000
2017-01-20 11:16:33.000
600
null
null

2
1
Withdrawal
2017-01-21 11:16:33.000
2017-01-20 11:16:33.000
100
null
1

3
2
Deposit
2017-02-23 11:16:33.000
2017-02-23 11:16:33.000
500
null
2

4
1
Withdrawal
2017-01-24 11:16:33.000
2017-01-21 11:16:33.000
150
2
3

5
1
Withdrawal
2017-01-26 11:16:33.000
2017-01-24 11:16:33.000
150
4
4

6
2
Withdrawal
2017-02-27 11:16:33.000
2017-02-23 11:16:33.000
200
null
5

7
1
Withdrawal
2017-01-28 11:16:33.000
2017-01-26 11:16:33.000
10
5
6

8
1
Withdrawal
2017-01-30 11:16:33.000
2017-01-28 11:16:33.000
10
7
7

9
1
Withdrawal
2017-01-31 11:16:33.000
2017-01-30 11:16:33.000
10
8
8


SELECT t.*,
                   SUM(CASE WHEN prev_st_id = prev_id THEN 0 ELSE 1 END) OVER (ORDER BY id) as grp
            FROM (SELECT t.*,
                         LAG(id) OVER (PARTITION BY SessionId, TransactionType ORDER BY id) as prev_st_id,
                         LAG(id) OVER (ORDER BY id) as prev_id
                  FROM Transactions t
                 ) t


Id
SessionId
TransactionType
DateTimeEnd
DateStart
Rank
prev_st_id
prev_id
grp


1
1
Deposit
2017-01-20 11:16:33.000
2017-01-20 11:16:33.000
600
null
null
1

2
1
Withdrawal
2017-01-21 11:16:33.000
2017-01-20 11:16:33.000
100
null
1
2

3
2
Deposit
2017-02-23 11:16:33.000
2017-02-23 11:16:33.000
500
null
2
3

4
1
Withdrawal
2017-01-24 11:16:33.000
2017-01-21 11:16:33.000
150
2
3
4

5
1
Withdrawal
2017-01-26 11:16:33.000
2017-01-24 11:16:33.000
150
4
4
4

6
2
Withdrawal
2017-02-27 11:16:33.000
2017-02-23 11:16:33.000
200
null
5
5

7
1
Withdrawal
2017-01-28 11:16:33.000
2017-01-26 11:16:33.000
10
5
6
6

8
1
Withdrawal
2017-01-30 11:16:33.000
2017-01-28 11:16:33.000
10
7
7
6

9
1
Withdrawal
2017-01-31 11:16:33.000
2017-01-30 11:16:33.000
10
8
8
6


SELECT t.*,
             SUM(Rank) OVER (PARTITION BY SessionId, TransactionType, grp) as sumRank
      FROM (SELECT t.*,
                   SUM(CASE WHEN prev_st_id = prev_id THEN 0 ELSE 1 END) OVER (ORDER BY id) as grp
            FROM (SELECT t.*,
                         LAG(id) OVER (PARTITION BY SessionId, TransactionType ORDER BY id) as prev_st_id,
                         LAG(id) OVER (ORDER BY id) as prev_id
                  FROM Transactions t
                 ) t
           ) t


Id
SessionId
TransactionType
DateTimeEnd
DateStart
Rank
prev_st_id
prev_id
grp
sumRank


1
1
Deposit
2017-01-20 11:16:33.000
2017-01-20 11:16:33.000
600
null
null
1
600

2
1
Withdrawal
2017-01-21 11:16:33.000
2017-01-20 11:16:33.000
100
null
1
2
100

4
1
Withdrawal
2017-01-24 11:16:33.000
2017-01-21 11:16:33.000
150
2
3
4
300

5
1
Withdrawal
2017-01-26 11:16:33.000
2017-01-24 11:16:33.000
150
4
4
4
300

7
1
Withdrawal
2017-01-28 11:16:33.000
2017-01-26 11:16:33.000
10
5
6
6
30

8
1
Withdrawal
2017-01-30 11:16:33.000
2017-01-28 11:16:33.000
10
7
7
6
30

9
1
Withdrawal
2017-01-31 11:16:33.000
2017-01-30 11:16:33.000
10
8
8
6
30

3
2
Deposit
2017-02-23 11:16:33.000
2017-02-23 11:16:33.000
500
null
2
3
500

6
2
Withdrawal
2017-02-27 11:16:33.000
2017-02-23 11:16:33.000
200
null
5
5
200


SELECT t.*,
             SUM(Rank) OVER (PARTITION BY SessionId, TransactionType, grp) as sumRank
      FROM (SELECT t.*,
                   SUM(CASE WHEN prev_st_id = prev_id THEN 0 ELSE 1 END) OVER (ORDER BY id) as grp
            FROM (SELECT t.*,
                         LAG(id) OVER (PARTITION BY SessionId, TransactionType ORDER BY id) as prev_st_id,
                         LAG(id) OVER (ORDER BY id) as prev_id
                  FROM Transactions t
                 ) t
           ) t ORDER BY id


Id
SessionId
TransactionType
DateTimeEnd
DateStart
Rank
prev_st_id
prev_id
grp
sumRank


1
1
Deposit
2017-01-20 11:16:33.000
2017-01-20 11:16:33.000
600
null
null
1
600

2
1
Withdrawal
2017-01-21 11:16:33.000
2017-01-20 11:16:33.000
100
null
1
2
100

3
2
Deposit
2017-02-23 11:16:33.000
2017-02-23 11:16:33.000
500
null
2
3
500

4
1
Withdrawal
2017-01-24 11:16:33.000
2017-01-21 11:16:33.000
150
2
3
4
300

5
1
Withdrawal
2017-01-26 11:16:33.000
2017-01-24 11:16:33.000
150
4
4
4
300

6
2
Withdrawal
2017-02-27 11:16:33.000
2017-02-23 11:16:33.000
200
null
5
5
200

7
1
Withdrawal
2017-01-28 11:16:33.000
2017-01-26 11:16:33.000
10
5
6
6
30

8
1
Withdrawal
2017-01-30 11:16:33.000
2017-01-28 11:16:33.000
10
7
7
6
30

9
1
Withdrawal
2017-01-31 11:16:33.000
2017-01-30 11:16:33.000
10
8
8
6
30


SELECT SessionId, TransactionType, DateTimeEnd,DateStart, sumRank
FROM (SELECT t.*,
             SUM(Rank) OVER (PARTITION BY SessionId, TransactionType, grp) as sumRank
      FROM (SELECT t.*,
                   SUM(CASE WHEN prev_st_id = prev_id THEN 0 ELSE 1 END) OVER (ORDER BY id) as grp
            FROM (SELECT t.*,
                         LAG(id) OVER (PARTITION BY SessionId, TransactionType ORDER BY id) as prev_st_id,
                         LAG(id) OVER (ORDER BY id) as prev_id
                  FROM Transactions t
                 ) t
           ) t
     ) t
WHERE prev_st_id <> prev_id OR prev_st_id IS NULL;



SessionId
TransactionType
DateTimeEnd
DateStart
sumRank


1
Deposit
2017-01-20 11:16:33.000
2017-01-20 11:16:33.000
600

1
Withdrawal
2017-01-21 11:16:33.000
2017-01-20 11:16:33.000
100

1
Withdrawal
2017-01-24 11:16:33.000
2017-01-21 11:16:33.000
300

1
Withdrawal
2017-01-28 11:16:33.000
2017-01-26 11:16:33.000
30

2
Deposit
2017-02-23 11:16:33.000
2017-02-23 11:16:33.000
500

2
Withdrawal
2017-02-27 11:16:33.000
2017-02-23 11:16:33.000
200
Id	SessionId	TransactionType	DateTimeEnd	DateStart	Rank
1	1	Deposit	2017-01-20 11:16:33.000	2017-01-20 11:16:33.000	600
2	1	Withdrawal	2017-01-21 11:16:33.000	2017-01-20 11:16:33.000	100
3	2	Deposit	2017-02-23 11:16:33.000	2017-02-23 11:16:33.000	500
4	1	Withdrawal	2017-01-24 11:16:33.000	2017-01-21 11:16:33.000	150
5	1	Withdrawal	2017-01-26 11:16:33.000	2017-01-24 11:16:33.000	150
6	2	Withdrawal	2017-02-27 11:16:33.000	2017-02-23 11:16:33.000	200
7	1	Withdrawal	2017-01-28 11:16:33.000	2017-01-26 11:16:33.000	10
8	1	Withdrawal	2017-01-30 11:16:33.000	2017-01-28 11:16:33.000	10
9	1	Withdrawal	2017-01-31 11:16:33.000	2017-01-30 11:16:33.000	10