CREATE TABLE test
(
id integer,
date text,
title text,
stock text
);
INSERT INTO public.test (id, date, title, stock) VALUES (1, '01.01.2022', 'Title 1', 'N');
INSERT INTO public.test (id, date, title, stock) VALUES (2, '02.01.2022', 'Title 1', 'N');
INSERT INTO public.test (id, date, title, stock) VALUES (3, '03.01.2022', 'Title 1', 'N');
INSERT INTO public.test (id, date, title, stock) VALUES (4, '04.01.2022', 'Title 1', 'Y');
INSERT INTO public.test (id, date, title, stock) VALUES (5, '05.01.2022', 'Title 1', 'Y');
INSERT INTO public.test (id, date, title, stock) VALUES (6, '06.01.2022', 'Title 1', 'N');
INSERT INTO public.test (id, date, title, stock) VALUES (7, '08.01.2022', 'Title 1', 'N');
INSERT INTO public.test (id, date, title, stock) VALUES (8, '09.01.2022', 'Title 1', 'N');
INSERT INTO public.test (id, date, title, stock) VALUES (9, '15.01.2022', 'Title 1', 'N');
SELECT
*,
CASE
WHEN stock = 'N' THEN
to_date(date, 'DD.MM.YYYY') - to_date(first_value(date) OVER (PARTITION BY title,batch ORDER BY date), 'DD.MM.YYYY')
ELSE 0 END AS "doos"
FROM (
SELECT
*,
COALESCE(CASE WHEN stock = 'Y' THEN id ELSE lag(id, rn::int) OVER (PARTITION BY title ORDER BY date) END) AS batch
FROM (
SELECT
*,
CASE
WHEN stock = 'N' THEN
row_number() OVER (PARTITION BY title,stock,s1 - s2 ORDER BY date)
ELSE NULL END AS "rn"
FROM
(SELECT
*,
ROW_NUMBER() OVER (PARTITION BY title ORDER BY date) AS "s1",
ROW_NUMBER() OVER (PARTITION BY title,stock ORDER BY date) AS "s2"
FROM
test) AS "s"
) AS r
) AS q
ORDER BY
id
Telegram: DB developers channel