Calculate the Days Between Orders Your SQL Server 2017 database has a Sales table with 100 million rows that contains CustomerID, OrderID, and OrderDate columns. You would like to return a list of orders (all columns). For each order, return the number of days until the next order is placed. For the last order, return a 0 instead of NULL. Assuming a helpful index is in place, which query will give you that answer and also performs the best? Query #1 SELECT CustomerID, OrderID, OrderDate,
DATEDIFF(DAY,OrderDate, LEAD(OrderDate,1,OrderDate)
OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS DaysUntilNextOrder
FROM Sales; Query #2 SELECT CustomerID, OrderID, OrderDate,
DATEDIFF(DAY,OrderDate, LAG(OrderDate,-1,0)
OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS DaysUntilNextOrder
FROM Sales; Query #3 SELECT O.CustomerID, O.OrderID, O.OrderDate,
ISNULL(DATEDIFF(DAY,O.OrderDate,NextOrder.OrderDate),0) AS DaysUntilNextOrder
FROM Sales AS O
OUTER APPLY (
SELECT TOP(1) OrderDate
FROM Sales AS I
WHERE I.CustomerID = O.CustomerID
AND I.OrderID > O.OrderID
ORDER BY I.OrderID) AS NextOrder;Answer: Query #1 Explanation: In 2012, Microsoft introduced the window functions, LAG and LEAD. LAG returns an expression from a prior row in the partition while LEAD returns a later row in the partition. You can use these functions instead of joining a table to itself when needing expressions from a different row, and the performance is great. The default for LAG and LEAD is one row away from the current row, but by using the optional parameter OFFSET, it will return rows further away from the current row. OFFSET must be 0 or greater, so Query #2 will not run. You can't use LAG to return expressions from a later row in the partition. A second optional parameter DEFAULT, replaces any NULLs returned by LAG or LEAD. Query #1 is the correct answer using LEAD. By using the OrderDate from the current row, NULL will be replaced by zero. Instead, you can use the ISNULL or COALSCE function. Query #3 will return the correct results, but it will not perform well. Ref: Introduction to T-SQL Window Functions Discuss this question and answer on the forums |
