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 as
select *
from (values (1, 1, 'A', '0101', 1),
(1, 1, 'A', '0102', 1),
(2, 3, 'A', '0105', 2),
(2, 5, 'A', '0105', 2),
(3, 7, 'B', '0101', 1),
(3, 8, 'B', '0104', 1)
) v(product, type_id, user_id, Date, desired)
6 rows affected
select t.*
from (select t.*,
rank() over (partition by user_id order by min_date) as seqnum
from (select t.*,
min(date) over (partition by user_id, product) as min_date
from t
) t
) t
where seqnum = 1;
| product | type_id | user_id | date | desired | min_date | seqnum |
|---|---|---|---|---|---|---|
| 1 | 1 | A | 0101 | 1 | 0101 | 1 |
| 1 | 1 | A | 0102 | 1 | 0101 | 1 |
| 3 | 7 | B | 0101 | 1 | 0101 | 1 |
| 3 | 8 | B | 0104 | 1 | 0101 | 1 |
select t.*
from (select t.*,
min(date) over (partition by user_id, product) as min_date_up,
min(date) over (partition by user_id) as min_date_u
from t
) t
where min_date_u = min_date_up;
| product | type_id | user_id | date | desired | min_date_up | min_date_u |
|---|---|---|---|---|---|---|
| 1 | 1 | A | 0101 | 1 | 0101 | 0101 |
| 1 | 1 | A | 0102 | 1 | 0101 | 0101 |
| 3 | 7 | B | 0101 | 1 | 0101 | 0101 |
| 3 | 8 | B | 0104 | 1 | 0101 | 0101 |