Explanation/Reference:
Explanation:
LAG accesses data from a previousrow in the same result set without the use of a self-join in SQL Server
2016. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
Use ORDER BY Year, not ORDER BY Territory.
Example: The following example uses the LAG function to return the difference in sales quotas for a specific employee over previous years. Notice that because there is no lag valueavailable for the first row, the default of zero (0) is returned.
USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');
Incorrect Answers:
A, D: LEAD accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row.
Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.
B: Use ORDER BY Year, not ORDER BY Territory.
References: https://msdn.microsoft.com/en-us/library/hh231256.aspx