I have been enjoying the interactive code challenge courses on LinkedIn Learning. I just ran into a point of contention of sorts with the solution for a challenge on the SQL Practice: Window Function course.
Here’s the problem: Given the entity relationship diagram (as shown below) your goal is to identify trends in customer ordering behavior by analyzing consecutive order total differences.
Now, I have some practice in using WINDOW and LAG functions as well as CTEs, so here's the query I came up with:
WITH order_price AS (
SELECT DISTINCT o.ORDERID,
SUM(d.PRICE) OVER(
PARTITION BY o.ORDERID
) AS ThisOrderPrice, o.orderdate
FROM Orders o
JOIN OrdersDishes od ON o.ORDERID = od.ORDERID
JOIN Dishes d ON od.DISHID = d.DISHID
WHERE OrderDate >= '2022-01-01'
ORDER BY o.ORDERID
)
SELECT ORDERID, ThisOrderPrice,
LAG(ThisOrderPrice) OVER (ORDER BY orderdate) - ThisOrderPrice AS DiffFromPrev
FROM order_price
Here's a side-by-side comparison of the output I received and the expected output,
So, what is it? Is it just a difference in implementation? Or is it a misinterpretation of the problem?