SQL Server 2019

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

SELECT @@VERSION;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE test
( 
  rn     INTEGER NOT NULL,
  name   VARCHAR (8), 
  p_date DATE NOT NULL
);
   
INSERT INTO test(rn, name, p_date)
VALUES 
(1,  'MERCEDES', '2018-01-01'), 
(2,  'SEAT',     '2018-02-01'), 
(3,  'MERCEDES', '2018-04-01'),
(4,  'BMW',      '2018-01-01'), 
(5,  'MERCEDES', '2018-01-01'), 
(6,  'MERCEDES', '2018-01-05'),
(7,  'MERCEDES', '2018-01-09'), 
(8,  'BMW',      '2017-12-01'), 
(9,  'BMW',      '2017-12-05'),
(10, 'SEAT',     '2017-12-01'), 
(11, 'BMW',      '2017-01-01'), 
(12, 'AUDI',     '2017-12-01'),
(13, 'BMW',      '2017-12-29'), 
(14, 'BMW',      '2018-01-01'), 
-- (15,  NULL,      '2018-03-01'),  -- not inserted!
(16, 'SEAT',     '2018-04-01'), 
(17, 'SEAT',     '2018-05-01'), 
(18, 'SEAT',     '2018-07-01'),
(19, 'BMW',      '2018-01-01'), 
-- (20,  NULL,      '2018-01-01'),  -- not inserted!
(21, 'MERCEDES', '2018-03-01'),    
(22, 'MERCEDES', '2018-01-05'), 
(23, 'MERCEDES', '2018-04-09');
21 rows affected

SELECT * FROM test ORDER BY rn;


rn
name
p_date


1
MERCEDES
2018-01-01

2
SEAT
2018-02-01

3
MERCEDES
2018-04-01

4
BMW
2018-01-01

5
MERCEDES
2018-01-01

6
MERCEDES
2018-01-05

7
MERCEDES
2018-01-09

8
BMW
2017-12-01

9
BMW
2017-12-05

10
SEAT
2017-12-01

11
BMW
2017-01-01

12
AUDI
2017-12-01

13
BMW
2017-12-29

14
BMW
2018-01-01

16
SEAT
2018-04-01

17
SEAT
2018-05-01

18
SEAT
2018-07-01

19
BMW
2018-01-01

21
MERCEDES
2018-03-01

22
MERCEDES
2018-01-05

23
MERCEDES
2018-04-09


SELECT
  name, MAX(rn2) AS cnt, fv_sd, lv_sd
FROM
(
  SELECT
    *, 
    FIRST_VALUE(p_date) OVER (PARTITION BY sd ORDER BY sd) AS fv_sd,
     LAST_VALUE(p_date) OVER (PARTITION BY sd ORDER BY sd) AS lv_sd,
    ROW_NUMBER() OVER (PARTITION BY sd ORDER BY rn) AS rn2
  FROM
  (
    SELECT
      rn, name, lgn, c_diff,  
      SUM(c_diff) OVER (ORDER BY rn, name) AS sd,
      p_date
    FROM
    (
      SELECT 
        rn,
        name, LAG(name) OVER (ORDER BY rn) AS lgn, 
        CASE 
          WHEN name = LAG(name) OVER (ORDER BY rn) 
            THEN 0
            ELSE 1
        END AS c_diff,
        p_date
      FROM test
      -- ORDER BY rn
    ) AS t_01
    -- ORDER BY rn
  ) AS t_02
-- ORDER BY rn
) AS t_03
GROUP BY name, fv_sd, lv_sd
HAVING MAX(rn2) > 1
ORDER BY cnt DESC, fv_sd, lv_sd, name;


name
cnt
fv_sd
lv_sd


MERCEDES
3
2018-01-01
2018-01-09

MERCEDES
3
2018-03-01
2018-04-09

SEAT
3
2018-04-01
2018-07-01

BMW
2
2017-12-01
2017-12-05

BMW
2
2017-12-29
2018-01-01

rn	name	p_date
1	MERCEDES	2018-01-01
2	SEAT	2018-02-01
3	MERCEDES	2018-04-01
4	BMW	2018-01-01
5	MERCEDES	2018-01-01
6	MERCEDES	2018-01-05
7	MERCEDES	2018-01-09
8	BMW	2017-12-01
9	BMW	2017-12-05
10	SEAT	2017-12-01
11	BMW	2017-01-01
12	AUDI	2017-12-01
13	BMW	2017-12-29
14	BMW	2018-01-01
16	SEAT	2018-04-01
17	SEAT	2018-05-01
18	SEAT	2018-07-01
19	BMW	2018-01-01
21	MERCEDES	2018-03-01
22	MERCEDES	2018-01-05
23	MERCEDES	2018-04-09