A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.
You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit, LEAD(Profit, 1,0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
B. SELECT Territory, Year, Profit, LAG(Profit, 1,0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
C. SELECT Territory, Year, Profit, LAG(Profit, 1,0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
D. SELECT Territory, Year, Profit, LEAD(Profit, 1,0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
Correct Answer: C
Explanation/Reference:
Explanation:
LAG accesses data from a previous row 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. Usethis 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 fora specific employee over previous years. Notice that because there is no lag value available for the first row, the default of zero (0) is returned.
USE AdventureWorks2012;
GO
SELECT BusinessEntitylD, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntitylD = 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/librarv/hh231256.aspx