SQL difference between array values of current row with previous row

Question

I have the following table

Date	SKUs
2022-02-01	A, B, C
2022-02-02	B, C, D
2022-02-03	C, D, E
2022-02-04	C, D
2022-02-05	G, H

Now I need to find the difference b/w SKUs of current date with previous date (something like lag function in Postgres)

Date	SKUs	SKU Diff
2022-02-01	A, B, C
2022-02-02	B, C, D	D
2022-02-03	C, D, E	E
2022-02-04	C, D
2022-02-05	G, H	G, H

SKUs column is of array type

I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. — a_horse_with_no_name, Feb 17 '22 at 11:04
'something like lag function in Postgres)' - there is a lag function in mysql version 8 or above so what version are you on? — P.Salmon, Feb 17 '22 at 11:04
Does this answer your question? [Array difference in postgresql](https://stackoverflow.com/questions/55304197/array-difference-in-postgresql) — OM Bharatiya, Feb 17 '22 at 11:09

score 0 · Answer 1 · answered Feb 17 '22 at 21:07

with SKU as 
(
select Dates,trim(a.value) as SKUs from SKUs s
cross apply string_split(s.SKUs,',') a
),
SKU_Diff as
(
SELECT dates, SKU_Diff FROM (SELECT Dates,STRING_AGG(SKUs,', ') as SKU_Diff, ROW_NUMBER() OVER(ORDER BY dates) AS rn 
FROM SKU s
WHERE s.SKUs not in (SELECT s1.skus FROM SKU s1 WHERE s.dates = dateadd(day,1,s1.dates))
GROUP BY dates) as a WHERE rn > 1
)
SELECT  s.Dates,SKUs,isnull(SKU_Diff,'') as SKU_Diff 
FROM SKUs s
left join SKU_Diff sd on s.dates = sd.dates;

SQL difference between array values of current row with previous row

1 Answers1