Quiz Team Standings With Shared Places — SQL Medium Problem

Problem statement

At the end of the college quiz, teams are placed by points. Teams with equal points share a place, and the next lower total takes the next place with no gap: 42.5, 42.5, 38 are places 1, 1, 2. A team that was disqualified has NULL points and gets no place.

Return every team that has points, with columns team_name, points and standing. Order the rows by points highest first, then by team_name.

Tables

Table: QuizScore

Primary key: team_id.

One row per team; team names are unique. points can be a half point, and is NULL for a disqualified team.

Examples

Example 1

QuizScore

Output

How to solve Quiz Team Standings With Shared Places

Three ranking functions differ only on ties. ROW_NUMBER() gives tied rows different numbers, RANK() gives them the same number and then skips (1, 1, 3), and DENSE_RANK() gives them the same number and does not skip (1, 1, 2). The statement describes the last one, so the whole answer is DENSE_RANK() OVER (ORDER BY points DESC).

Disqualified teams must be removed in WHERE. If they stayed, the NULLs would sort last in a descending order and receive the last place instead of none. The window is computed after WHERE, so filtering first keeps the places of the other teams unchanged. The final ORDER BY points DESC, team_name fixes the order of tied teams; the window's own ORDER BY does not order the output.

Without window functions, a team's dense place is the number of distinct totals at or above its own: a correlated COUNT(DISTINCT r.points) … WHERE r.points >= q.points, or the same count from a self join grouped by team. Both are quadratic in the number of teams, while the window version is one sort — but they show exactly what a dense rank means. A NULL points never satisfies >=, so the self join drops those teams on its own.

Reference solution (MySQL)

SELECT team_name, points,
       DENSE_RANK() OVER (ORDER BY points DESC) AS standing
FROM QuizScore
WHERE points IS NOT NULL
ORDER BY points DESC, team_name

Another way

SELECT q.team_name, q.points,
       (SELECT COUNT(DISTINCT r.points) FROM QuizScore r WHERE r.points >= q.points) AS standing
FROM QuizScore q
WHERE q.points IS NOT NULL
ORDER BY q.points DESC, q.team_name

Another way

SELECT a.team_name, a.points, COUNT(DISTINCT b.points) AS standing
FROM QuizScore a
JOIN QuizScore b ON b.points >= a.points
GROUP BY a.team_id, a.team_name, a.points
ORDER BY a.points DESC, a.team_name