By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
WITH purchase( empid, productid, purchasedate ) AS
(
SELECT 1153, 66, '2012-03-21' UNION ALL
SELECT 1153, 66, '2012-06-22' UNION ALL
SELECT 1153, 66, '2012-08-24' UNION ALL
SELECT 1153, 66, '2012-10-11' UNION ALL
SELECT 1153, 28, '2012-11-09' UNION ALL
SELECT 1153, 66, '2013-01-04' UNION ALL
SELECT 1153, 66, '2013-03-21' UNION ALL
SELECT 1153, 66, '2013-04-20' UNION ALL
SELECT 1153, 29, '2018-12-20' UNION ALL
SELECT 1153, 29, '2019-03-21' UNION ALL
SELECT 1153, 29, '2019-03-22' UNION ALL
SELECT 1153, 29, '2019-04-17' UNION ALL
SELECT 1153, 29, '2019-06-17' UNION ALL
SELECT 1153, 29, '2019-07-11' UNION ALL
SELECT 1153, 66, '2019-08-21' UNION ALL
SELECT 1153, 66, '2019-10-01' UNION ALL
SELECT 1153, 66, '2019-11-07' UNION ALL
SELECT 1153, 66, '2019-12-04' UNION ALL
SELECT 1153, 66, '2020-01-14'
)
SELECT empid, productid, MIN(purchasedate) as purchasedate
FROM (
SELECT
p.*,
ROW_NUMBER() OVER ( PARTITION BY empid ORDER BY purchasedate ) rn1,
ROW_NUMBER() OVER ( PARTITION BY empid, productid ORDER BY purchasedate ) rn2
FROM purchase p
) t
GROUP BY empid, productid, rn1 - rn2
ORDER BY purchasedate
| empid | productid | purchasedate |
|---|---|---|
| 1153 | 66 | 2012-03-21 |
| 1153 | 28 | 2012-11-09 |
| 1153 | 66 | 2013-01-04 |
| 1153 | 29 | 2018-12-20 |
| 1153 | 66 | 2019-08-21 |