0

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

Samkeet Jain
  • 1
  • I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. –  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
  • @P.Salmon I'm on Postgresql 9.x – Samkeet Jain Feb 17 '22 at 11:06
  • 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

1 Answers1

0
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;
Sanjeev
  • 1
  • 1