SQL Server 2022

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

CREATE TABLE mytable (
  stayid varchar(20),
  cid	varchar(20),
  arrival	date,
  departure	date,
  country	varchar(20),
  state	varchar(20),
  pid	varchar(20),
  email	varchar(20),
  phone int
);

insert into mytable values
('ab1','a101','2023-01-01','2023-04-05','US','ohio','ly1','101@gmail.com','12345'),
('ab2','a101','2023-04-05','2023-04-06','US','ohio','ly1','101@gmail.com','12345'),
('ab7','a106','2023-01-20','2023-04-26','US','Texas','ly6','106@gmail.com','87468'),
('ab8','a107','2023-01-20','2023-04-26','US','Texas','ly7','107@gmail.com','55555'),
('ab10','a109','2023-01-20','2023-04-28','US','Texas','ly9','109@gmail.com','55555');
5 rows affected

select *, DATEDIFF(day, departure, lead(departure, 1, departure) over (partition by cid order by departure)) as date_diff
from mytable;


stayid
cid
arrival
departure
country
state
pid
email
phone
date_diff


ab1
a101
2023-01-01
2023-04-05
US
ohio
ly1
101@gmail.com
12345
1

ab2
a101
2023-04-05
2023-04-06
US
ohio
ly1
101@gmail.com
12345
0

ab7
a106
2023-01-20
2023-04-26
US
Texas
ly6
106@gmail.com
87468
0

ab8
a107
2023-01-20
2023-04-26
US
Texas
ly7
107@gmail.com
55555
0

ab10
a109
2023-01-20
2023-04-28
US
Texas
ly9
109@gmail.com
55555
0


-- top be removed
with cte as (
  select *, 
      lead(stayid) over (partition by cid order by departure) as lead_stayid,
      DATEDIFF(day, departure, lead(departure) over (partition by cid order by departure)) as date_diff
  from mytable
)
select *
from cte
where date_diff is not null and date_diff < 30


stayid
cid
arrival
departure
country
state
pid
email
phone
lead_stayid
date_diff


ab1
a101
2023-01-01
2023-04-05
US
ohio
ly1
101@gmail.com
12345
ab2
1


with cte as (
  select *, 
      lead(stayid) over (partition by cid order by departure) as lead_stayid,
      DATEDIFF(day, departure, lead(departure) over (partition by cid order by departure)) as date_diff
  from mytable
)
delete from mytable
Where stayid in (
                select stayid
                from cte
                where date_diff is not null and date_diff < 30
                )
1 rows affected

select *
from mytable


stayid
cid
arrival
departure
country
state
pid
email
phone


ab2
a101
2023-04-05
2023-04-06
US
ohio
ly1
101@gmail.com
12345

ab7
a106
2023-01-20
2023-04-26
US
Texas
ly6
106@gmail.com
87468

ab8
a107
2023-01-20
2023-04-26
US
Texas
ly7
107@gmail.com
55555

ab10
a109
2023-01-20
2023-04-28
US
Texas
ly9
109@gmail.com
55555

stayid	cid	arrival	departure	country	state	pid	email	phone	date_diff
ab1	a101	2023-01-01	2023-04-05	US	ohio	ly1	101@gmail.com	12345	1
ab2	a101	2023-04-05	2023-04-06	US	ohio	ly1	101@gmail.com	12345	0
ab7	a106	2023-01-20	2023-04-26	US	Texas	ly6	106@gmail.com	87468	0
ab8	a107	2023-01-20	2023-04-26	US	Texas	ly7	107@gmail.com	55555	0
ab10	a109	2023-01-20	2023-04-28	US	Texas	ly9	109@gmail.com	55555	0