By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table users (
user_id int,
subscription_date date,
service varchar(10));
insert into users values
(001,'2024-01-01','basic'),
(002,'2024-01-01','plus'),
(002,'2023-01-01','plus'),
(003,'2024-01-01','premium'),
(003,'2024-01-01','premium');
Records: 5 Duplicates: 0 Warnings: 0
select * from users;
| user_id | subscription_date | service |
|---|---|---|
| 1 | 2024-01-01 | basic |
| 2 | 2024-01-01 | plus |
| 2 | 2023-01-01 | plus |
| 3 | 2024-01-01 | premium |
| 3 | 2024-01-01 | premium |
select user_id,
subscription_date,
service
from (
select *,
count(*) over (partition by user_id,subscription_date,service order by user_id asc ) as cnt
from users
) sub
where cnt > 1;
| user_id | subscription_date | service |
|---|---|---|
| 3 | 2024-01-01 | premium |
| 3 | 2024-01-01 | premium |
select user_id,service,subscription_date
from users
group by user_id,service,subscription_date
having count(*) > 1;
| user_id | service | subscription_date |
|---|---|---|
| 3 | premium | 2024-01-01 |