SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Data (id INT, [date] DATE, points INT); INSERT INTO Data VALUES (1, '2021-10-01', 0), (1, '2021-10-02', 0), (1, '2021-10-03', 1), (1, '2021-10-04', 2), (1, '2021-10-05', 3), (1, '2021-10-06', 0), (1, '2021-10-07', 1), (1, '2021-10-08', 1); WITH D AS ( SELECT *, CASE WHEN COALESCE(LAG(points) OVER (PARTITION BY id ORDER BY date), 0) = 0 AND points > 0 THEN date END "start", CASE WHEN COALESCE(LEAD(points) OVER (PARTITION BY id ORDER BY date), 0) = 0 AND points > 0 THEN date END "end" FROM Data ) SELECT id, "start", -- "end", LEAD("end") OVER (PARTITION BY id ORDER BY date) "end", DATEDIFF(DAY, "start", LEAD("end") OVER (PARTITION BY id ORDER BY date)) "diff" FROM D WHERE ("start" IS NOT NULL OR "end" IS NOT NULL);