determine since date from record set in PostgreSQL

Question

I have table customer details having three columns

empid(integer),productid (integer) and purchasedate(date)

having records like

empid productid purchasedate
1153    67      2010-01-14 
1153    67      2010-02-15
1153    67      2010-02-25
1153    67      2010-03-18
1153    67      2010-04-08
1153    67      2010-04-28
1153    28      2011-07-21
1153    28      2011-09-12
1153    28      2011-11-11
1153    28      2011-12-22
1153    28      2012-01-21
1153    66      2012-03-21
1153    66      2012-06-22
1153    66      2012-08-24
1153    66      2012-10-11
1153    28      2012-11-09
1153    66      2013-01-04
1153    66      2013-03-21
1153    66      2013-04-20
1153    29      2013-06-12
1153    29      2013-08-01
1153    29      2013-10-25
1153    29      2014-01-24
1153    29      2017-01-30
1153    67      2017-06-19
1153    67      2017-10-11
1153    67      2017-12-02
1153    28      2018-09-15
1153    28      2018-10-20
1153    29      2018-12-20
1153    29      2019-03-21
1153    29      2019-03-22
1153    29      2019-04-17
1153    29      2019-06-17
1153    29      2019-07-11
1153    66      2019-08-21
1153    66      2019-10-01
1153    66      2019-11-07
1153    66      2019-12-04
1153    66      2020-01-14 

now i have to determine since purchase date which is purchasing same product since date from max purchase date product id.

max purchase date 2020-01-14 and productid is 66 and i want determine since date that is

empid pruductid purchasedate
1153    66      2019-08-21

how to determine above record in PostgreSQL.

Answer 1

Although you can treat this as a gaps-and-islands problem, you can just look at the last product and get all the final rows that match.

select p.*
from (select p.*,
             count(*) filter (where productid <> last_productid) over (partition by empid order by purchasedate) as num_otherproducts
      from (select p.*,
                   first_value(productid) over (partition by empid order by purchasedate desc) as last_productid
            from purchase p 
           ) p
     ) p
where num_otherproducts = 0;

If you used a gap-and-islands approach, I would recommend doing this as:

SELECT p.*
FROM (SELECT p.*,
             ROW_NUMBER() OVER ( PARTITION BY empid ORDER BY purchasedate desc ) as seqnum1,
             ROW_NUMBER() OVER ( PARTITION BY empid, productid ORDER BY purchasedate desc ) as seqnum2
      FROM purchase p
     ) p
WHERE seqnum1 = seqnum2
ORDER BY p.purchasedate;

The idea is to enumerate the rows for each empid from the end. And to enumerate the rows for each empid/productid combination. When these are the same, you have the final product.

Answer 2

Seems you need to determine each empid & productid combinations group containing thier respective minimum purchase dates .

Then, you can resolve it by using gaps-and-islands technique through row_number() analytic function as

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;

Demo