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.
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;