You want to find each student's score rank within their class and also show the average score of their class. Which SQL query correctly uses OVER with PARTITION BY to achieve this?
hard📝 Application Q15 of 15
SQL - Window Functions Fundamentals
You want to find each student's score rank within their class and also show the average score of their class. Which SQL query correctly uses OVER with PARTITION BY to achieve this?
ASELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores;
BSELECT student_id, class, score, RANK() OVER (ORDER BY score DESC PARTITION BY class) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores;
CSELECT student_id, class, score, RANK() PARTITION BY class ORDER BY score DESC AS rank_in_class, AVG(score) OVER (class) AS avg_class_score FROM student_scores;
DSELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score) AS rank_in_class, AVG(score) OVER (PARTITION BY class ORDER BY score) AS avg_class_score FROM student_scores;
Step-by-Step Solution
Solution:
Step 1: Use correct syntax for RANK() and AVG() with PARTITION BY
Both functions must have OVER (PARTITION BY class ...). RANK() orders descending for highest score first; AVG() just partitions by class.
Step 2: Check each option's correctness
SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores; correctly uses RANK() OVER (PARTITION BY class ORDER BY score DESC) and AVG(score) OVER (PARTITION BY class). Others have syntax errors or wrong order.
Final Answer:
SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores; -> Option A
Quick Check:
Use OVER (PARTITION BY ...) with correct order and parentheses [OK]
Quick Trick:Use separate OVER clauses with PARTITION BY for each window function [OK]
Common Mistakes:
Mixing ORDER BY and PARTITION BY order inside OVER
Missing parentheses around PARTITION BY
Using PARTITION BY without OVER
Master "Window Functions Fundamentals" in SQL
9 interactive learning modes - each teaches the same concept differently
