Unacademy mid PERCENT_RANKWindow FunctionsRANKORDER BYWHERE

For each student in mock_test_scores who has a non-NULL score, calculate their percentile as PERCENT_RANK() expressed as a percentage (0 to 100, rounded to 2 decimal places) such that the highest-scoring student ends up with a percentile near 100 and the lowest-scoring student ends up near 0 — matching how 'percentile' is normally understood ('I scored better than X% of test-takers'). Exclude Vikram, whose score is NULL, entirely. Return student_name, score, and percentile, sorted by score descending.

Unacademy · Mock Test Percentiles — practice this real-world SQL scenario live in your browser.

📖 Story Unacademy · Mock Test Percentiles

Thursday evening at Unacademy's Test Analytics team. Right after every mock test, students want one number that tells them where they stand: 'I beat this percentage of everyone who took the test.' The mock_test_scores table logs every registered student, but one of them, Vikram, registered and never actually attempted the test — his score is NULL, not zero, and he shouldn't factor into anyone's percentile at all. A first attempt at the percentile calculation looked fine syntactically and ran without any error, but the numbers it produced were backwards: the highest scorer in the batch came out with the worst percentile in the report.

🎯 Your Mission

📋 Table Structure

🗂 mock_test_scores

student_id INTEGER 1

student_name TEXT Aarav

score INTEGER 92

⚡ Step-by-Step Walkthrough

Look at the raw scores, including the no-show

query.sql

SELECT *
FROM mock_test_scores
ORDER BY score DESC;

💡 Explanation

Eight students registered, but only seven have an actual score — Vikram's score is NULL because he registered for the test and never showed up to attempt it, which is a completely different situation from a student who attempted it and scored a genuine 0.
Priya and Karthik are tied at exactly 85 — any percentile calculation has to give them the same percentile, since nothing in the data distinguishes how well they actually did relative to each other.
Scores range from Aarav's 92 down to Sneha's 55, with a real gap between Meera's 78 and the tied pair at 85 — that gap is exactly what a percentile number needs to reflect, roughly, once it's calculated.
Nothing is ranked yet — this step only confirms the shape of the real data before deciding how NULL and the tie should be handled.

The trap: PERCENT_RANK ordered by score DESC puts the top scorer at 0

query.sql

SELECT student_name, score,
       ROUND(PERCENT_RANK() OVER (ORDER BY score DESC) * 100, 2) AS percentile_bug
FROM mock_test_scores
WHERE score IS NOT NULL
ORDER BY score DESC;

💡 Explanation

WHERE score IS NOT NULL runs before PERCENT_RANK ever sees a row, so Vikram is correctly gone from this result and from the total count PERCENT_RANK divides by — his absence never gets treated as a score of 0.
PERCENT_RANK() is defined as (rank - 1) / (total_rows - 1), where rank comes from ordering by whatever ORDER BY inside OVER() says. Ordered by score DESC, the highest scorer is rank 1, and (1 - 1) / 6 is exactly 0 — Aarav, the best performer in the batch, gets a percentile_bug of 0.
Sneha, the lowest scorer, ends up at the opposite end: her rank is 7 out of 7, giving (7 - 1) / 6 = 1, or 100%. The direction is completely inverted from what 'percentile' means in everyday language, where a high percentile is supposed to mean a strong result, not a weak one.
Nothing about this query is a syntax error or a NULL-handling mistake — it runs cleanly and produces a plausible-looking table of numbers between 0 and 100. The only thing wrong is which direction ORDER BY was pointed, which is exactly the kind of bug that's easy to ship because the output never looks obviously broken.

Fix it: order by score ASC so a high score means a high percentile

query.sql

SELECT student_name, score,
       ROUND(PERCENT_RANK() OVER (ORDER BY score ASC) * 100, 2) AS percentile
FROM mock_test_scores
WHERE score IS NOT NULL
ORDER BY score DESC;

💡 Explanation

Flipping the window's ORDER BY to ASC changes what 'rank 1' means: now the lowest score is rank 1, so PERCENT_RANK counts up from the bottom of the batch instead of down from the top — a high score now genuinely earns a high percentile.
Aarav's score of 92 is now the highest-ranked row in the ASC ordering, landing at (7 - 1) / 6 = 1, a percentile of 100 — exactly the 'better than everyone else' result a top scorer should see.
Sneha's 55 is now rank 1 in the ASC ordering, giving (1 - 1) / 6 = 0 — the lowest scorer correctly lands at the bottom of the percentile scale instead of the top.
The outer ORDER BY score DESC is unrelated to the ASC inside OVER() — it only controls the order rows are displayed in the final result, purely for readability; it has no effect on how PERCENT_RANK itself was calculated, which is why Priya and Karthik still both land on the same percentile despite the display order sorting them by score.

▶ Practice this live ← Browse all questions