как-то так решается задача, идея в том, чтобы избавиться от столбца active_to дополнив изначальные данные строками:
WITH
"prices" (row_id, product_id, active_from, active_to, price) AS (
VALUES
(1, 1, '2019-01-01'::date, null, 200),
(2, 1, '2019-10-01'::date, null, 150),
(3, 1, '2019-11-01'::date, '2019-11-30'::date, 100),
(4, 2, '2019-01-01'::date, null, 200),
(5, 2, '2019-06-01'::date, '2019-08-31'::date, 150),
(6, 2, '2019-07-01'::date, '2019-09-30'::date, 140),
(7, 2, '2019-11-01'::date, null, 100),
(8, 3, '2019-01-01'::date, null, 200),
(9, 3, '2019-06-01'::date, '2019-10-31'::date, 150),
(10, 3, '2019-07-01'::date, '2019-09-30'::date, 140),
(11, 3, '2019-11-01'::date, null, 100)
)
SELECT
u."row_id",
u."product_id",
u."active_from",
u."active_to",
u."price",
lag(u."price") OVER (PARTITION BY u."product_id" ORDER BY u."effective_date") as "previous_price",
u."effective_date"
FROM (
SELECT "u".*,
coalesce("u"."row_id" = lead("u"."row_id") OVER (PARTITION BY "u"."product_id" ORDER BY "u"."effective_date"), FALSE) AS "exclude_row"
FROM (
SELECT "p1".*, "p1"."active_from" AS "effective_date"
FROM "prices" "p1"
UNION ALL
(
SELECT DISTINCT ON ("prev"."product_id") "prev".*, ("p"."active_to" + '1 day'::interval)::date AS "effective_date"
FROM "prices" "p"
LEFT JOIN "prices" "prev" ON "prev"."product_id" = "p"."product_id"
AND "p"."active_to" > "prev"."active_from"
AND "p"."active_to" < coalesce("prev"."active_to", now())
WHERE "p"."active_to" IS NOT NULL
ORDER BY "prev"."product_id", "prev"."active_from" DESC
)
) "u"
) u
WHERE NOT u."exclude_row"
ORDER BY "product_id", "effective_date"