How can I use SQL query to determine if rows related to a specific user has sequential year dates?

The table looks like this:

What logic can show me that user A has 3 dates (counted) that are sequential with no gaps in the year, and User B has 2 dates (counted) but does have a gap in the year?

Want the result could look like this:

I simply have no idea how to make this work. I have some working knowledge of SQL Query, but don’t know where to start on this one. Thank you.

>Solution :

You could use a CTE or sub-query to determine the number of years between Max and Min, and then compare to count using a case expression.

create table my_data (
  user_id varchar(5), 
  some_date date
  );
  
insert into my_data values 
('A', '2022-08-05'),
('A', '2021-08-05'),
('A', '2020-08-06'),
('B', '2022-08-05'),
('B', '2020-07-07');

select user_id, total_rows as total, 
 case when total_rows = max_minus_min then 'Y' else 'N' end as Sequential
from (
  select user_id, year(max(some_date)) - year(min(some_date)) + 1 as max_minus_min, 
   count(*) as total_rows
    from my_data
  group by user_id
)z
order by 1;

fiddle