SQL WINDOW FUNC when no unique partition

Question

I have this table:

Date	ID	Product_A_Count	Product_B_Count
1	A	1	2
2	A	1	2
3	A	2	1
4	A	1	2

I want to convert it to:

Start_Date	End_Date	ID	Product_A_Count	Product_B_Count
1	2	A	1	2
3	3	A	2	1
4	4	A	1	2

Is there a way to do it by using just SQL? I've been trying out row_number and lag but none looks right (because the partition by combination of ID, Product_A_Count and Product_B_Count is not unique).

Many thanks for all suggestions!!

Answer 1

This is a type of gap-and-islands problem. In this case, the difference of row numbers is probably the simplest solution:

select id, product_a_count, product_b_count, min(date), max(date)
from (select t.*,
             row_number() over (partition by id order by date) as seqnum,
             row_number() over (partition by id, product_a_count, product_b_count order by date) as seqnum_2
      from t
     ) t
group by id, product_a_count, product_b_count, (seqnum - seqnum_2);

There might be simpler solution if, for instance, the dates are all regularly spaced.