viji viji - 1 month ago 9
SQL Question

SQL query to find maximum of 3 transactions after the specific part repair

My data is similiar to this example which is very huge so idealy require an efficient code. I want to find those transactions(max of 3) after the charger repair.

TRANSACTION_ID REPAIR_DATE CUSTOMER_ID COMPONENT LABOR_CODE_DESC ....
28289 6/25/2015 AH123 LAPTOP CHARGER REPAIR
28235 6/29/2015 AH123 LAPTOP CHIP REPLACE
258978 6/27/2013 HW687 PHONE TOUCH SCREEN
28223 6/2/2014 AH123 LAPTOP BATTERY REPAIR
215678 9/7/2014 HW687 PHONE SIM REPAIR
527808 7/30/2016 HW687 LAPTOP BATTERY REPAIR
567976 7/28/2014 HW687 LAPTOP CHARGER REPAIR
7678698 8/68/2015 AH123 LAPTOP BATTERY REPAIR
9987908 5/7/2006 TU890 PHONE SIM REPAIR
.....


OUTPUT
TRANSACTION_ID REPAIR_DATE CUSTOMER_ID COMPONENT LABOR_CODE_DESC ....
28235 6/29/2015 AH123 LAPTOP CHIP REPLACE
7678698 8/68/2015 AH123 LAPTOP BATTERY REPAIR
527808 7/30/2016 HW687 LAPTOP BATTERY REPAIR
215678 9/7/2014 HW687 PHONE SIM REPAIR

.....


SO all I want is those customer id transactions which have had charger repair and a maximum of 3 transactions after the charger repair date

SELECT TRANSACTION_ID, REPAIR_DATE,CUSTOMER_ID,LABOR_CODE_DESC from table
where customer_id IN (SELECT CUSTOMER_ID from table where LABOR_CODE_DESC
like '%CHARGER REPAIR%')


Not sure how I can expand to get maximum of 3 transactions after CHARGER REPAIR

Answer

This returns the three rows after a charger repair date:

SELECT *
FROM tab
QUALIFY 
   -- check if any of the three previous rows contains 'CHARGER REPAIR'
   Max(CASE WHEN LABOR_CODE_DESC = 'CHARGER REPAIR' THEN 1 ELSE 0 END)
   Over (PARTITION BY CUSTOMER_ID
         ORDER BY REPAIR_DATE
         ROWS BETWEEN 3 Preceding AND 1 Preceding) = 1

Edit:

To get only rows with a repair date within the next 10 days:

   -- check if any of the previous rows contains 'CHARGER REPAIR'
   -- and the current REPAIR_DATE is within 10 days after that repair
   Max(CASE WHEN LABOR_CODE_DESC = 'CHARGER REPAIR' THEN REPAIR_DATE END)
   Over (PARTITION BY CUSTOMER_ID
         ORDER BY REPAIR_DATE
         ROWS BETWEEN Unbounded Preceding AND 1 Preceding) >= REPAIR_DATE - 10