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 ;
Telegram: DB developers channel