SQLize Online / PHPize Online  /  SQLtest Online

ROLLBACK; BEGIN; CREATE TEMP TABLE stocks ( id SERIAL not null PRIMARY KEY, date DATE NOT NULL, title VARCHAR NOT NULL, stock CHAR(1) ) ON COMMIT DROP; INSERT INTO stocks(date, title, stock) VALUES ('2022-01-01', 'title 1', 'N'), ('2022-01-02', 'title 1', 'N'), ('2022-01-03', 'title 1', 'N'), ('2022-01-04', 'title 1', 'Y'), ('2022-01-05', 'title 1', 'Y'), ('2022-01-06', 'title 1', 'N'), ('2022-01-08', 'title 1', 'N'), ('2022-01-09', 'title 1', 'N'), ('2022-01-15', 'title 1', 'N'), ('2022-01-02', 'title 2', 'N'), ('2022-01-03', 'title 2', 'Y'), ('2022-01-04', 'title 2', 'Y'), ('2022-01-08', 'title 2', 'N'), ('2022-01-09', 'title 2', 'N'); SELECT * FROM stocks; WITH cte AS ( SELECT id, date, title, stock, (CASE WHEN stock = 'N' AND LAG(stock) over w = 'N' THEN date - LAG(date) over w WHEN stock = 'N' AND LAG(stock) over w = 'Y' OR LAG(stock) over w IS NULL THEN 0 WHEN stock = 'Y' THEN NULL END) AS delta, SUM(CASE WHEN stock = 'Y' THEN 1 ELSE 0 END) OVER w AS part_key FROM stocks WINDOW w AS (PARTITION BY title ORDER BY date ASC) ORDER BY id) SELECT id, date, title, stock, delta, part_key, SUM(delta) OVER w2 AS doos FROM cte WINDOW w2 AS (PARTITION BY title, part_key ORDER BY date ASC ) ORDER BY id; ROLLBACK ;