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 preceding year. Which Transact-SQL query should you use?
A.
SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits
B.
SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits
C.
SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits
D.
SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits
Explanation:
http://msdn.microsoft.com/en-us/library/hh231256.aspx Reference: http://msdn.microsoft.com/en-us/library/hh213125.aspx
The Correct answer to this question is “C” because the question is clear about the period which the next year as seen here “You need to create a
report that displays the profits made by each territory for each year and its preceding year” and the analytical function LEAD() will return the value in the next row of the current value which also explain here: https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql
0
8
“preceding” (from the question text) means Previous.
I think the the alias “Next Year” in the query is written mistakenly.
B is right.
1
0