Click here to Skip to main content
15,850,359 members
Articles / Programming Languages / SQL

SQL Puzzle: How to Rank Student Scores

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
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.

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 students 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 students 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.

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.

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.

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.

Rank Student Scores using SQL Window Function

The post SQL Puzzle: How to Rank Student Scores appeared first on Essential SQL.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States 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.
- 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/

Comments and Discussions

 
QuestionWhy these character is coming    Pin
Tridip Bhattacharjee27-Mar-17 23:44
professionalTridip Bhattacharjee27-Mar-17 23:44 
QuestionChar set Pin
Nelek26-Mar-17 19:53
protectorNelek26-Mar-17 19:53 
AnswerRe: Char set Pin
essentialSQL8-Nov-17 7:52
essentialSQL8-Nov-17 7:52 
Hi - This is content is coming across from a RSS feed. The original content is hosted in a WordPress blog.

I think the CODE PROJECT team does a really great job consuming the content and fixing issues!
Kris - www.essentialsql.com

GeneralRe: Char set Pin
Nelek8-Nov-17 8:53
protectorNelek8-Nov-17 8:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.