In this puzzle, we're going to work through a problem to identify the top and bottom 25 percent to rank student scores.
Solving puzzles is a great way to learn SQL. Nothing beats practicing what you've learned.
SQL Puzzle Question
In today's puzzle, assume you’ve been asked by the head of the Mathematics department to provide him three lists of students ranked by score:
- Students whose scores are in the top 25%
- Students whose scores are in the bottom 25%
- Students in the “middle.”
The table you’ll query is named TestScore
and contains the following fields:
StudentID
(Primary Key) StudentName
(Varchar
) Score
If you wish, you can use the following as your sample data. Rather than creating a table, I declared a table variable. You can add this into query window and run your queries from there:
DECLARE @TestScore TABLE
(
StudentID int,
StudentName Varchar(40),
Score float
)
Insert INTO @TestScore values (1, 'Han Solo', 98.0);
Insert INTO @TestScore values (2, 'The Fly', 92.0);
Insert INTO @TestScore values (3, 'Darth Vader', 83.0);
Insert INTO @TestScore values (4, 'Luke Skywalker', 78.0);
Insert INTO @TestScore values (5, 'Homer Simpson', 54.0);
Insert INTO @TestScore values (6, 'Porky Pig', 65.0);
Insert INTO @TestScore values (7, 'James T. Kirk', 91.0);
Insert INTO @TestScore values (8, 'Spock', 93.0);
Insert INTO @TestScore values (9, 'Batman', 85.0);
Insert INTO @TestScore values (10, 'Robin', 87.0);
Insert INTO @TestScore values (11, 'Superman', 94.0);
Insert INTO @TestScore values (12, 'Road Runner', 74.0);
Insert INTO @TestScore values (13, 'Wilie Coyote', 79.0);
Insert INTO @TestScore values (14, 'Ant Man', 82.0);
Insert INTO @TestScore values (15, 'Cool Hand Luke', 92.0);
Insert INTO @TestScore values (16, 'C3PO', 88.0);
Insert INTO @TestScore values (17, 'BB8', 92.0);
Insert INTO @TestScore values (18, 'Flash Gordon', 60.0);
Insert INTO @TestScore values (19, 'Bugs Bunny', 84.0);
Insert INTO @TestScore values (20, 'Gordon Geko', 75.0);
Can you provide the queries to get the answers to questions 1-3?
BONUS! Combine the queries into a single result with the following columns:
Ranking
Student Name
Score
Where ranking would be one of three values: Top25
, Bottom25
, Middle
.
Answer to Question 1
In order to answer the first question, take advantage of the TOP PERCENT clause. When used with ordered data, the TOP PERCENT
can be used to select student
s whose scores fall within the top 25 percent of all scores.
SELECT TOP(25) PERCENT WITH TIES StudentName, Score
FROM @TestScore
ORDER BY Score DESC
By ordering the data in descending order, the highest scores are listed first. Also, we use the WITH TIES
clause to ensure that student
s with the same score as those in the top 25% aren't left out. In my testing, I found that if I didn't do this, then BB8
wasn't included.
Below are the results I got:
Answer to Question 2
To obtain this answer, I used the same query, but this time I ordered the scores in Ascending order.
SELECT TOP(25) PERCENT WITH TIES StudentName, Score
FROM @TestScore
ORDER BY Score ASC
This means the lowest scores are listed first and will be included in my TOP(25) PERCENT
result.
Answer to Question 3
To get the middle, first combined the results from question 1 and 2. I then use a subquery to exclude these students from my result. What remained was the middle.
The combined results are in bold.
The subquery to exclude them is in italics.
SELECT StudentName, Score
FROM @TestScore
WHERE StudentID NOT IN
(SELECT TOP(25) PERCENT WITH TIES StudentID
FROM @TestScore
ORDER BY Score DESC
UNION
SELECT TOP(25) PERCENT WITH TIES StudentID
FROM @TestScore
ORDER BY Score ASC
)
ORDER BY Score DESC;
I could have also used the EXCEPT
operator instead of a subquery as follows:
WITH TopAndBottomScore (StudentID, StudentName, Score)
AS
(
SELECT TOP(25) PERCENT WITH TIES StudentID, StudentName, Score
FROM @TestScore
ORDER BY Score Desc
UNION
SELECT TOP(25) PERCENT WITH TIES StudentID, StudentName, Score
FROM @TestScore
ORDER BY Score Asc
)
SELECT StudentName, Score
FROM @TestScore
EXCEPT
SELECT StudentName, Score
FROM TopAndBottomScore
ORDER BY Score DESC;
I used a Common Table Expression to contain the UNION of the top and bottom scores.
Then, these scores were removed from all the scores using the EXCEPT operator.
Here are the results I got:
Answer to the Bonus Question
To answer the bonus question, I took an altogether different approach. Rather than rely on TOP
, I took advantage of the CUME_DIST
window function to calculate the relative position of scores.
The OVER
clause specifies that the window is all students sorted by score. Notice that there is no PARTITION BY
clause, just the ORDER BY
.
WITH CumScore (StudentName, Score, CumScore)
AS
(
SELECT StudentName,
Score,
CUME_DIST () OVER (ORDER BY SCORE)
FROM @TestScore
)
SELECT CASE
WHEN CumScore <= .25 THEN 'Bottom25'
WHEN CumScore >= .75 THEN 'TOP25'
ELSE 'Middle'
END as Ranking,
StudentName,
Score
FROM CumScore
ORDER BY Score;
To get the Ranking
, we use a CASE
statement to compare the returned CumScore
.
The post SQL Puzzle: How to Rank Student Scores appeared first on Essential SQL.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/