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 PrevProfit
FROM Profits
B.
SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit
FROM Profits
C.
SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit
FROM Profits
D.
SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit
FROM Profits
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/hh231256.aspx
Reference:
http://msdn.microsoft.com/en-us/library/hh213125.aspx
C: LAG – to retrieve info for previous period, LEAD – for next one. Order by year