SQL from per day table to date range table transformation

Question

I need to transform the following input table to the output table where output table will have ranges instead of per day data.

Input:

Asin day is_instock
--------------------    
 A1   1      0
 A1   2      0
 A1   3      1
 A1   4      1
 A1   5      0
 A2   3      0
 A2   4      0

Output:

asin start_day end_day is_instock
---------------------------------
 A1      1        2       0
 A1      3        4       1
 A1      5        5       0
 A2      3        4       0

Answer 1

You are looking for an operator described in the temporal data literature, and "best known" as PACK.

This operator was not made part of the SQL standard (SQL:2011) that introduced the temporal features of the literature into the language, so there's extremely little chance you're going to find anything to support you in any SQL product/dialect.

Boils down to : you'll have to write out the algorithm to do the PACKing yourself.