All sample questions / SQL
What does SELECT school, grade_level, student, score, RANK() OVER (PARTITION BY ... return? SQL Window Functions
Block 6, Window Functions, question 6 of the SQL sample
CREATE TABLE grade_results (id INTEGER, school TEXT, grade_level INTEGER, student TEXT, score INTEGER);
INSERT INTO grade_results VALUES
(1, 'lakeview', 5, 'Nia', 88),
(2, 'lakeview', 5, 'Omar', 95),
(3, 'lakeview', 6, 'Pat', 79),
(4, 'lakeview', 6, 'Qi', 84);
SELECT school, grade_level, student, score,
RANK() OVER (PARTITION BY school, grade_level ORDER BY score DESC) AS rnk
FROM grade_results
ORDER BY school, grade_level, rnk
What does this query return?
Show the answer and why each option is right or wrong
Answer, D. lakeview | 5 | Omar | 95 | 1 lakeview | 5 | Nia | 88 | 2 lakeview | 6 | Qi | 84 | 1 lakeview | 6 | Pat | 79 | 2
- A. lakeview | 5 | Omar | 95 | 0 lakeview | 5 | Nia | 88 | 1 lakeview | 6 | Qi | 84 | 0 lakeview | 6 | Pat | 79 | 1
- This starts each partition at 0 instead of 1, treating RANK as a zero based index rather than the one based position SQL actually assigns.
- B. lakeview | 5 | Omar | 95 | 2 lakeview | 5 | Nia | 88 | 1 lakeview | 6 | Qi | 84 | 2 lakeview | 6 | Pat | 79 | 1
- This orders score ascending instead of descending inside each grade, so the lower score ranks first rather than the higher one.
- C. lakeview | 5 | Omar | 95 | 1 lakeview | 5 | Nia | 88 | 2 lakeview | 6 | Qi | 84 | 3 lakeview | 6 | Pat | 79 | 4
- This partitions by school alone, letting grade 5 and grade 6 share one continuous ranking instead of each grade level restarting its own count.
- D. lakeview | 5 | Omar | 95 | 1 lakeview | 5 | Nia | 88 | 2 lakeview | 6 | Qi | 84 | 1 lakeview | 6 | Pat | 79 | 2
- Correct. Grade 5 and grade 6 at lakeview form separate partitions even though they share a school, so each grade ranks its own two students starting at 1.
A two column PARTITION BY forms a group for every distinct pair of values, so rows from grade 5 never compete with rows from grade 6 even under the same school, they are scored as if they belonged to entirely separate tables.
The answer above is what SQLite and DuckDB both returned when the snippet was run, not what someone expected it to do.
This question comes from a bank of 502 SQL interview questions, all built the same way, sold as one PDF with the full answer key.
Get the 502 SQL questions, 9 euros
Or SQL, Python, pandas and NumPy together, 1,636 questions, 19 euros
Or play the free SQL sample, twenty questions in the browser, also as a free PDF.
Previous question Next question
Did this save you a lookup? Buy me a coffee on Ko-fi.