CORRECT TEXT
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes
over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to
have a rank of 2, and the last 10 to have a rank of 3.
Which of the following SELECT statement would you use?
To answer, type the correct code in the answer area.
Answer:
Explanation:
SELECT TOP 30 PlayerName,
NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes
FROM WeeklyVotes
GROUP BY PlayerName
Use NTILE
0
0
I don’t think the answer will work, it will returns 30 records with all AveVotes = 1..
unless there are exactly 30 players in the table, the query won’t show the result correctly.
i think it should be something like:
WITH CTE AS
(SELECT TOP 30 PlayerName, AVG(Votes) AS AvgVotes
FROM WeeklyVotes
GROUP BY PlayerName
ORDER BY AvgVotes DESC
)
SELECT PlayerName, NTILE(3) OVER (ORDER BY AvgVotes DESC ) AS Rank
FROM CTE
0
0
Also, this isn’t taking into account the “over the last 12 months… is a WHERE clause needed?
0
0
WITH CTE AS
(SELECT TOP 30 PlayerName, AVG(Votes) AS AvgVotes
FROM WeeklyVotes
GROUP BY PlayerName
ORDER BY AvgVotes DESC
)
SELECT PlayerName, NTILE(3) OVER (ORDER BY AvgVotes DESC ) AS Rank
FROM CTE
WHERE [Week] BETWEEN 1 AND 52
0
3
WE Can also achieve using Row_number() function as see below
SELECT TOP 30 PlayerName,ROW_NUMBER() OVER(ORDER BY AVG(Votes) DESC) AS AVGvotes,Sum(Votes) [SUMofVotes] ,’ROW_NUMBER function’ name
FROM WeeklyVotes
GROUP BY PlayerName
0
0
Wonderful story, reckoned we could combine a couple of unrelated data, nonetheless genuinely worth taking a look, whoa did one particular study about Mid East has got additional problerms too.
0
0
The time to read or visit the content material or sites we’ve linked to beneath.
0
0
How about
with C as
(select TOP(30) PlayerName,AVG(Votes) as AvgVotes
FROM WeeklyVotes
WHERE Week in (SELECT DISTINCT TOP 52 Week From WeeklyVotes ORDER BY Week DESC)
GROUP BY PlayerName
ORDER BY AvgVotes DESC
)
SELECT PlayerName, AvgVotes,
NTILE(3) OVER (ORDER BY AvgVotes DESC) as RK
FROM C
ORDER BY RK;
1
0