All sample questions / SQL

What does SELECT team, name, hire_date, ROW_NUMBER() OVER (PARTITION BY team ... return? SQL Window Functions

Block 6, Window Functions, question 19 of the SQL sample

CREATE TABLE hires (id INTEGER, team TEXT, name TEXT, hire_date TEXT);
INSERT INTO hires VALUES
  (1, 'ops', 'Dez', '2019-06-15'),
  (2, 'ops', 'Cole', '2021-03-01'),
  (3, 'ops', 'Ann', '2020-01-10');


SELECT team, name, hire_date,
       ROW_NUMBER() OVER (PARTITION BY team ORDER BY hire_date ASC) AS seniority
FROM hires
ORDER BY name

What does this query return?

  1. A. ops | Ann | 2020-01-10 | 2 ops | Cole | 2021-03-01 | 1 ops | Dez | 2019-06-15 | 3
  2. B. ops | Ann | 2020-01-10 | 1 ops | Cole | 2021-03-01 | 2 ops | Dez | 2019-06-15 | 0
  3. C. ops | Ann | 2020-01-10 | 2 ops | Cole | 2021-03-01 | 3 ops | Dez | 2019-06-15 | 1
  4. D. ops | Ann | 2020-01-10 | 1 ops | Cole | 2021-03-01 | 2 ops | Dez | 2019-06-15 | 3
Show the answer and why each option is right or wrong

Answer, C. ops | Ann | 2020-01-10 | 2 ops | Cole | 2021-03-01 | 3 ops | Dez | 2019-06-15 | 1

A. ops | Ann | 2020-01-10 | 2 ops | Cole | 2021-03-01 | 1 ops | Dez | 2019-06-15 | 3
This ranks the most recent hire first, the opposite of ASC, so Cole would get seniority 1 instead of the last position that a newer hire date actually earns.
B. ops | Ann | 2020-01-10 | 1 ops | Cole | 2021-03-01 | 2 ops | Dez | 2019-06-15 | 0
This starts the count at 0 for the earliest hire instead of 1, treating ROW_NUMBER as a zero based index rather than the one based row position SQL defines.
C. ops | Ann | 2020-01-10 | 2 ops | Cole | 2021-03-01 | 3 ops | Dez | 2019-06-15 | 1
Correct. The window orders by hire_date, so Dez, the earliest hire, gets seniority 1, while the final ORDER BY name displays the rows alphabetically.
D. ops | Ann | 2020-01-10 | 1 ops | Cole | 2021-03-01 | 2 ops | Dez | 2019-06-15 | 3
This assumes seniority follows the alphabetical order the rows are displayed in, as if the window ORDER BY copied the outer ORDER BY name instead of using hire_date.

The ORDER BY inside OVER and the ORDER BY at the end of the query are unrelated, one decides the values a window function computes and the other decides the order rows are displayed in. Sorting the display alphabetically does not change which hire counts as the most senior.

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.