All sample questions / SQL
What does SELECT artist, track, play_count, DENSE_RANK() OVER (PARTITION BY ... return? SQL Window Functions
Block 6, Window Functions, question 16 of the SQL sample
CREATE TABLE track_plays (id INTEGER, artist TEXT, track TEXT, play_count INTEGER);
INSERT INTO track_plays VALUES
(1, 'nova', 'Echo', 5000),
(2, 'nova', 'Static', 7200),
(3, 'nova', 'Drift', 7200),
(4, 'nova', 'Halo', 4100);
SELECT artist, track, play_count,
DENSE_RANK() OVER (PARTITION BY artist ORDER BY play_count DESC) AS drnk
FROM track_plays
ORDER BY artist, drnk, track
What does this query return?
Show the answer and why each option is right or wrong
Answer, B. nova | Drift | 7200 | 1 nova | Static | 7200 | 1 nova | Echo | 5000 | 2 nova | Halo | 4100 | 3
- A. nova | Drift | 7200 | 1 nova | Static | 7200 | 1 nova | Echo | 5000 | 3 nova | Halo | 4100 | 4
- This is what RANK would produce, also tying the top two tracks at 1 but then skipping ahead to 3 for the next track instead of the gapless 2 DENSE_RANK gives.
- B. nova | Drift | 7200 | 1 nova | Static | 7200 | 1 nova | Echo | 5000 | 2 nova | Halo | 4100 | 3
- Correct. Drift and Static tie for the most plays and both get 1, then DENSE_RANK moves straight to 2 for Echo with no gap, and 3 for Halo.
- C. nova | Drift | 7200 | 1 nova | Static | 7200 | 2 nova | Echo | 5000 | 3 nova | Halo | 4100 | 4
- This is what ROW_NUMBER would produce, splitting the tied top tracks into two different numbers instead of letting them share the same rank.
- D. nova | Halo | 4100 | 1 nova | Echo | 5000 | 2 nova | Drift | 7200 | 3 nova | Static | 7200 | 3
- This orders play_count ascending instead of descending, so the least played track ranks first, the opposite of what DESC in the window ORDER BY specifies.
DENSE_RANK counts distinct play counts, not rows, which is why a tie for the top spot is immediately followed by the very next integer rather than a number that skips ahead to account for both tied rows.
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.