How to do a conditional max to return the last purchase from the customer for each warranty claim?

user_3879598 0
Viewed 42

Hi I have a data table that looks like this

OrderID Date Product Name CustomerID
476070 February 24,2021 WARRANTY CLAIM ABC123
476069 February 22,2021 PRODUCT C ABC123
476068 February 10,2021 WARRANTY CLAIM ABC123
476067 February 1,2021 WARRANTY CLAIM ABC123
476064 January 23,2021 PRODUCT B ABC123
476051 January 10,2021 WARRANTY CLAIM ABC123
475023 January 1 , 2021 PRODUCT A ABC123

What I am trying to achieve is for each warranty claim , I want to get the last product purchase of that customer.

So meaning that the output I want to achieve is like this

OrderID Date Product Name CustomerID LASTPRODUCTPURCHASE
476070 February 24,2021 WARRANTY CLAIM ABC123 PRODUCT C
476069 February 22,2021 PRODUCT C ABC123 NULL
476068 February 10,2021 WARRANTY CLAIM ABC123 PRODUCT B
476067 February 1,2021 WARRANTY CLAIM ABC123 PRODUCT B
476064 January 23,2021 PRODUCT B ABC123 NULL
476051 January 10,2021 WARRANTY CLAIM ABC123 PRODUCT A
475023 January 1 , 2021 PRODUCT A ABC123 NULL

I have tried LAG and MAXIF but it returns either the previous order or the latest order for that particular customer. I need to have the LAST product purchase of that customer and only applicable for warranty claim orders.

I am getting this output for the CTE solution

enter image description here

This is the query I ran :

WITH cte AS (
    SELECT *, MAX(CASE WHEN productName <> 'Warranty Service' OR productName <> 'Warranty Collect' OR productName <> 'Warranty Inspection' OR productName <> 'Delivery' THEN id END) OVER
                  (PARTITION BY vpn ORDER BY ordercompleted_at) MaxOrderID
    FROM baterikudatawarehouse.bateriku_production.BaterikuOrderTrafficJune2020
    where orderStatus = 'completed' and vpn = 'QMY70' order by ordercompleted_at DESC
)

SELECT t1.id ,t1.ordercompleted_at, t1.productName, t1.vpn,
       CASE WHEN t1.productName = 'Warranty Service' OR t1.productName = 'Warranty Collect' OR t1.productName = 'Delivery' OR t1.productName = 'Warranty Inspection'
            THEN t2.productName END AS LASTPRODUCTPURCHASE
FROM cte t1

INNER JOIN baterikudatawarehouse.bateriku_production.BaterikuOrderTrafficJune2020 t2
    ON t2.vpn = t1.vpn AND
       t2.id = t1.MaxOrderId ORDER BY t1.ordercompleted_at DESC;
google-bigquery postgresql sql
2 Answers

One approach, which first finds the latest orders for each customer, at each point in time:

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

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 yourTable t2
    ON t2.CustomerID = t1.CustomerID AND
       t2.OrderID = t1.MaxOrderId;

Note that the join back to the original table pulls in the latest product name from the most recent non warranty transaction.

user_18632290

here is another approach using lead :

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

here is db<>fiddle, include @Tim Biegeleisen answer which IMO a better choose if CTE is allow.
also I have add more rows with sample data in db<>fiddle for better examine.

user_144751840
Related