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?.
CREATE TABLE t (
customer VARCHAR(50),
product VARCHAR(50),
purchase_date DATE
);
CREATE TABLE
INSERT INTO t (customer, product, purchase_date)
VALUES
('vik', 'office', '2022-04-21'),
('vik', 'office2', '2021-02-21'),
('vik', 'office', '2021-01-21'),
('vik', 'office2', '2023-02-21'),
('abc', 'office', '2022-02-01'),
('abc', 'office', '2021-02-01');
INSERT 0 6
SELECT
customer, product, MIN(purchase_date) as purchase_date
FROM t
GROUP BY
customer, product
| customer | product | purchase_date |
|---|---|---|
| abc | office | 2021-02-01 |
| vik | office | 2021-01-21 |
| vik | office2 | 2021-02-21 |
SELECT 3
SELECT
*
FROM (
SELECT
customer, product, purchase_date
, row_number() over(partition by customer, product
order by purchase_date ASC) as rn
FROM t
) d
WHERE rn = 1
| customer | product | purchase_date | rn |
|---|---|---|---|
| abc | office | 2021-02-01 | 1 |
| vik | office | 2021-01-21 | 1 |
| vik | office2 | 2021-02-21 | 1 |
SELECT 3