15,850,359 members
Articles / Programming Languages / SQL

# SQL Puzzle: How to Rank Student Scores

Rate me:
25 Mar 2017MIT3 min read 9K   2   4
How to identify the top and bottom 25 percent to rank student scores

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:

1. Students whose scores are in the top 25%
2. Students whose scores are in the bottom 25%
3. 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:

SQL
```-- TEST DATA
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`.

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.

SQL
```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:

To obtain this answer, I used the same query, but this time I ordered the scores in Ascending order.

SQL
```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.

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.

SQL
```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:

SQL
```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`.

SQL
```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.

Written By
United States
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.

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.