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
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
Also, this isn’t taking into account the “over the last 12 months… is a WHERE clause needed?
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
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
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.
The time to read or visit the content material or sites we’ve linked to beneath.
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;