0% completed
Problem Statement
Table: Student
Each row in this table represents a student's performance in a specific subject, including their unique ID, name, score, and the subject ID they were evaluated in.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| score | int |
| subjectId | int |
+-------------+---------+
id is the primary key for this table.
subjectId is a foreign key referencing the id from the Subject table.
Table: Subject
This table lists all subjects offered, each with a unique ID and name.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key for this table.
Develop a solution to identify the top achievers in each subject, defined as students with scores in the top three unique scores for that subject.
Example
Input:
Student table: +----+--------+-------+-----------+ | id | name | score | subjectId | +----+--------+-------+-----------+ | 1 | Alice | 92 | 1 | | 2 | Bob | 85 | 2 | | 3 | Carol | 87 | 2 | | 4 | Derek | 95 | 1 | | 5 | Elisa | 88 | 1 | | 6 | Fiona | 92 | 1 | | 7 | George | 90 | 1 | +----+--------+-------+-----------+ Subject table: +----+---------+ | id | name | +----+---------+ | 1 | Math | | 2 | Science | +----+---------+
Output:
+---------+---------+-------+ | Subject | Student | Score | +---------+---------+-------+ | Math | Derek | 95 | | Math | Alice | 92 | | Math | Fiona | 92 | | Math | George | 90 | | Science | Carol | 87 | | Science | Bob | 85 | +---------+---------+-------+
In this example, the top achievers in each subject are determined based on their scores, with a maximum of three students recognized per subject if there are at least three unique scores.
Try It Yourself
Solution
To identify the top achievers in each subject, defined as students with scores in the top three unique scores for that subject, we can utilize SQL's window functions, specifically DENSE_RANK()
. This approach allows us to rank students based on their scores within each subject and then filter out those who fall within the top three ranks.
- Join Tables: Combine the
Student
andSubject
tables to associate each student's performance with the corresponding subject name. - Rank Scores Within Each Subject: Use the
DENSE_RANK()
window function to assign ranks to students based on their scores in descending order within each subject. - Filter Top Achievers: Select students whose ranks are within the top three for their respective subjects.
- Order the Results: Sort the final output by subject name and rank for organized presentation.
SQL Query
WITH RankedScores AS ( SELECT st.name AS Student, su.name AS Subject, st.score, DENSE_RANK() OVER (PARTITION BY st.subjectId ORDER BY st.score DESC) AS s_rank FROM Student st JOIN Subject su ON st.subjectId = su.id ) SELECT Subject, Student, score AS Score FROM RankedScores WHERE s_rank <= 3 ORDER BY Subject, s_rank;
Step-by-Step Approach
Step 1: Join Student and Subject Tables and Rank Scores
Combine the Student
and Subject
tables to associate each student's score with the corresponding subject. Then, rank the students within each subject based on their scores in descending order.
SQL Query:
WITH RankedScores AS ( SELECT st.name AS Student, su.name AS Subject, st.score, DENSE_RANK() OVER (PARTITION BY st.subjectId ORDER BY st.score DESC) AS s_rank FROM Student st JOIN Subject su ON st.subjectId = su.id )
Explanation:
WITH RankedScores AS (...)
:- Defines a Common Table Expression (CTE) named
RankedScores
to store the intermediate results.
- Defines a Common Table Expression (CTE) named
SELECT st.name AS Student, su.name AS Subject, st.score, DENSE_RANK() OVER (...) AS s_rank
:st.name AS Student
&su.name AS Subject
:- Selects the student's name and the subject's name, renaming them for clarity in the results.
st.score
:- Retrieves the student's score in the subject.
DENSE_RANK() OVER (PARTITION BY st.subjectId ORDER BY st.score DESC) AS s_rank
:- Assigns a rank to each student within their subject based on their score.
PARTITION BY st.subjectId
:- Ensures that ranking restarts for each subject.
ORDER BY st.score DESC
:- Orders the scores in descending order so that the highest score receives the top rank.
DENSE_RANK()
:- Assigns the same rank to students with identical scores, ensuring that ranks are consecutive without gaps.
FROM Student st JOIN Subject su ON st.subjectId = su.id
:- Performs an inner join between the
Student
andSubject
tables based on thesubjectId
to link each student's performance to the corresponding subject.
- Performs an inner join between the
Output After Step 1:
Assuming the example input, the RankedScores
CTE would produce:
+---------+---------+-------+--------+ | Student | Subject | score | s_rank | +---------+---------+-------+--------+ | Derek | Math | 95 | 1 | | Alice | Math | 92 | 2 | | Fiona | Math | 92 | 2 | | George | Math | 90 | 3 | | Elisa | Math | 88 | 4 | | Carol | Science | 87 | 1 | | Bob | Science | 85 | 2 | +---------+---------+-------+--------+
Step 2: Select Top Achievers Based on Rank
Retrieve the students who have ranks within the top three for their respective subjects, indicating they are among the top achievers.
SQL Query:
SELECT Subject, Student, score AS Score FROM RankedScores WHERE s_rank <= 3 ORDER BY Subject, s_rank;
Explanation:
SELECT Subject, Student, score AS Score
:- Chooses the subject name, student name, and their score for the final output.
FROM RankedScores
:- Utilizes the previously defined CTE
RankedScores
to access the ranked data.
- Utilizes the previously defined CTE
WHERE s_rank <= 3
:- Filters the records to include only those students whose rank is 3 or below within their subject, effectively selecting the top three unique scores.
ORDER BY Subject, s_rank
:- Sorts the results first by subject name and then by rank to present the top achievers in an organized manner.
Final Output:
Based on the example input, the final output would be:
+---------+---------+-------+ | Subject | Student | Score | +---------+---------+-------+ | Math | Derek | 95 | | Math | Alice | 92 | | Math | Fiona | 92 | | Math | George | 90 | | Science | Carol | 87 | | Science | Bob | 85 | +---------+---------+-------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible