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 and Subject 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.
• 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 and Subject tables based on the subjectId to link each student's performance to the corresponding subject.

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.
• 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    |
+---------+---------+-------+