Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tb 
(
OrderID	int,
Date      date,	
ProductName varchar(30),	
CustomerID varchar(30)
);
insert into tb values (476070,'February 24,2021','WARRANTY CLAIM','ABC123');
insert into tb values (476069,'February 22,2021','PRODUCT C','ABC123');
insert into tb values (476068,'February 10,2021','WARRANTY CLAIM','ABC123');
insert into tb values (476067,'February 1,2021','WARRANTY CLAIM','ABC123');
insert into tb values (476064,'January 23,2021','PRODUCT B','ABC123');
insert into tb values (476063,'January 22,2021','WARRANTY CLAIM','ABC122');
insert into tb values (476062,'January 22,2021','WARRANTY CLAIM','ABC122');
insert into tb values (476061,'January 22,2021','WARRANTY CLAIM','ABC122');
insert into tb values (476060,'January 21,2021','PRODUCT B','ABC122');
insert into tb values (476051,'January 10,2021','WARRANTY CLAIM','ABC123');
insert into tb values (475023,'January 1,2021','PRODUCT A','ABC123');
insert into tb values (475022,'January 1,2021','WARRANTY CLAIM','ABC122');
insert into tb values (475021,'January 1,2021','PRODUCT A','ABC122');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select orderid,Date,productname,customerid,
       case when productname = 'WARRANTY CLAIM' then
       max(lastpn)over(partition by customerid order by orderid) else null end as LASTPRODUCTPURCHASE 
       from (
             select tb.*, 
             case when lead(productname)over(partition by customerid order by OrderID desc) = 'WARRANTY CLAIM' then null 
             else lead(productname)over(partition by customerid order by OrderID desc) end as lastpn
             from tb
            ) a


orderid
date
productname
customerid
lastproductpurchase


475021
2021-01-01
PRODUCT A
ABC122
null

475022
2021-01-01
WARRANTY CLAIM
ABC122
PRODUCT A

476060
2021-01-21
PRODUCT B
ABC122
null

476061
2021-01-22
WARRANTY CLAIM
ABC122
PRODUCT B

476062
2021-01-22
WARRANTY CLAIM
ABC122
PRODUCT B

476063
2021-01-22
WARRANTY CLAIM
ABC122
PRODUCT B

475023
2021-01-01
PRODUCT A
ABC123
null

476051
2021-01-10
WARRANTY CLAIM
ABC123
PRODUCT A

476064
2021-01-23
PRODUCT B
ABC123
null

476067
2021-02-01
WARRANTY CLAIM
ABC123
PRODUCT B

476068
2021-02-10
WARRANTY CLAIM
ABC123
PRODUCT B

476069
2021-02-22
PRODUCT C
ABC123
null

476070
2021-02-24
WARRANTY CLAIM
ABC123
PRODUCT C


WITH cte AS (
    SELECT *, MAX(CASE WHEN ProductName <> 'WARRANTY CLAIM' THEN OrderID END) OVER
                  (PARTITION BY CustomerID ORDER BY Date) MaxOrderID
    FROM tb
)

SELECT t1.OrderID, t1.Date, t1.ProductName, t1.CustomerID,
       CASE WHEN t1.ProductName = 'WARRANTY CLAIM'
            THEN t2.ProductName END AS LASTPRODUCTPURCHASE
FROM cte t1
INNER JOIN tb t2
    ON t2.CustomerID = t1.CustomerID AND
       t2.OrderID = t1.MaxOrderId;


orderid
date
productname
customerid
lastproductpurchase


475021
2021-01-01
PRODUCT A
ABC122
null

475022
2021-01-01
WARRANTY CLAIM
ABC122
PRODUCT A

476060
2021-01-21
PRODUCT B
ABC122
null

476062
2021-01-22
WARRANTY CLAIM
ABC122
PRODUCT B

476063
2021-01-22
WARRANTY CLAIM
ABC122
PRODUCT B

476061
2021-01-22
WARRANTY CLAIM
ABC122
PRODUCT B

475023
2021-01-01
PRODUCT A
ABC123
null

476051
2021-01-10
WARRANTY CLAIM
ABC123
PRODUCT A

476064
2021-01-23
PRODUCT B
ABC123
null

476067
2021-02-01
WARRANTY CLAIM
ABC123
PRODUCT B

476068
2021-02-10
WARRANTY CLAIM
ABC123
PRODUCT B

476069
2021-02-22
PRODUCT C
ABC123
null

476070
2021-02-24
WARRANTY CLAIM
ABC123
PRODUCT C

orderid	date	productname	customerid	lastproductpurchase
475021	2021-01-01	PRODUCT A	ABC122	null
475022	2021-01-01	WARRANTY CLAIM	ABC122	PRODUCT A
476060	2021-01-21	PRODUCT B	ABC122	null
476061	2021-01-22	WARRANTY CLAIM	ABC122	PRODUCT B
476062	2021-01-22	WARRANTY CLAIM	ABC122	PRODUCT B
476063	2021-01-22	WARRANTY CLAIM	ABC122	PRODUCT B
475023	2021-01-01	PRODUCT A	ABC123	null
476051	2021-01-10	WARRANTY CLAIM	ABC123	PRODUCT A
476064	2021-01-23	PRODUCT B	ABC123	null
476067	2021-02-01	WARRANTY CLAIM	ABC123	PRODUCT B
476068	2021-02-10	WARRANTY CLAIM	ABC123	PRODUCT B
476069	2021-02-22	PRODUCT C	ABC123	null
476070	2021-02-24	WARRANTY CLAIM	ABC123	PRODUCT C