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

The table looks like this:

User Date
A 2022-08-05
A 2021-08-05
A 2020-08-06
B 2022-08-05
B 2020-07-07

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:

User Count Sequential
A 3 Y
B 2 N

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
order by 1;
user_id total Sequential
A 3 Y
B 2 N


Leave a Reply