SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE store (
  store_id	int,
  city_id	int,
  store_type varchar(10)
);

insert into store values
(1,	1,	'regular'),
(2,	1,	'regular'),
(3,	1,	'regular'),
(50,	1,	'regular'),
(51,	1,	'express'),
(55,	1,	'express'),
(58,	1,	'express'),
(70,	1,	'express'),
(71,	2,	'regular'),
(75,	2,	'regular'),
(78,	2,	'regular'),
(80,	2,	'regular'),
(85,	2,	'regular'),
(90,	2,	'regular'),
(91,	1,	'regular'),
(95,	1,	'regular'),
(97,	1,	'regular'),
(100,	1,	'regular'),
(105,	1,	'regular');
19 rows affected

select *, row_number() over (order by store_id) 
          - row_number() over (partition by city_id, store_type order by store_id) as grp
from store


store_id
city_id
store_type
grp


51
1
express
4

55
1
express
4

58
1
express
4

70
1
express
4

1
1
regular
0

2
1
regular
0

3
1
regular
0

50
1
regular
0

91
1
regular
10

95
1
regular
10

97
1
regular
10

100
1
regular
10

105
1
regular
10

71
2
regular
8

75
2
regular
8

78
2
regular
8

80
2
regular
8

85
2
regular
8

90
2
regular
8


with cte as (
  select *, row_number() over (order by store_id) 
          - row_number() over (partition by city_id, store_type order by store_id) as grp
  from store
)
SELECT MIN(store_id) AS min_store_id,
       MAX(store_id) AS max_store_id,
       max(city_id) as city_id,
       max(store_type) as store_type
FROM cte
group by grp


min_store_id
max_store_id
city_id
store_type


1
50
1
regular

51
70
1
express

71
90
2
regular

91
105
1
regular

store_id	city_id	store_type	grp
51	1	express	4
55	1	express	4
58	1	express	4
70	1	express	4
1	1	regular	0
2	1	regular	0
3	1	regular	0
50	1	regular	0
91	1	regular	10
95	1	regular	10
97	1	regular	10
100	1	regular	10
105	1	regular	10
71	2	regular	8
75	2	regular	8
78	2	regular	8
80	2	regular	8
85	2	regular	8
90	2	regular	8