Una isla es una secuencia de filas consecutivas, como los días en que un producto estuvo sin existencias. Un hueco es una interrupción entre ellas. El truco clásico para encontrar islas usa ROW_NUMBER:
SELECT product_id, day,
day - CAST(ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY day) AS INTEGER) AS island
FROM stock_levels
WHERE units = 0;
Dentro de una secuencia de días consecutivos, la fecha aumenta en 1 y el número de fila aumenta en 1, por lo que day - row_number se mantiene constante. Si falta un día, la fecha da un salto, pero el número de fila no, lo que inicia un nuevo valor. Agrupa por (product_id, island) para obtener el inicio, el final y la duración de cada secuencia.
Los huecos son más fáciles: compara cada fila con la anterior usando LAG y conserva los casos en los que la diferencia sea mayor que un día.
Cada lectura junto a la anterior; un salto de más de un día es un intervalo sin datos.