Bird
Raised Fist0
SQLquery~30 mins

ROW_NUMBER function in SQL - Mini Project: Build & Apply

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Using the ROW_NUMBER Function in SQL
📖 Scenario: You work at a bookstore that wants to list books with a unique rank number based on their sales. This helps the store see which books are the top sellers.
🎯 Goal: Create a SQL query that assigns a row number to each book ordered by the number of sales, starting from 1 for the highest-selling book.
📋 What You'll Learn
Create a table called books with columns id, title, and sales.
Insert exactly three rows into the books table with specified values.
Write a query that uses the ROW_NUMBER() function to assign a rank to each book ordered by sales in descending order.
Select the id, title, sales, and the row number as rank.
💡 Why This Matters
🌍 Real World
Ranking items by sales or popularity is common in business reports, leaderboards, and dashboards.
💼 Career
Understanding ROW_NUMBER() helps in writing advanced SQL queries for data analysis and reporting roles.
Progress0 / 4 steps
1
Create the books table
Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as text, and sales as an integer.
SQL
Hint

Use CREATE TABLE books and define the columns with their types. Make id the primary key.

2
Insert book data
Insert exactly three rows into the books table with these values: (1, 'The Great Gatsby', 30), (2, '1984', 45), and (3, 'To Kill a Mockingbird', 25).
SQL
Hint

Use INSERT INTO books (id, title, sales) VALUES followed by the three tuples.

3
Write the ROW_NUMBER query
Write a SQL query that selects id, title, sales, and a new column called rank which uses the ROW_NUMBER() function ordered by sales in descending order from the books table.
SQL
Hint

Use ROW_NUMBER() OVER (ORDER BY sales DESC) to assign ranks starting from the highest sales.

4
Complete the ranking query
Add an ORDER BY rank clause at the end of the query to show the books sorted by their rank in ascending order.
SQL
Hint

Use ORDER BY rank to sort the results by the row number.

Practice

(1/5)
1. What does the ROW_NUMBER() function do in SQL?
easy
A. Counts the total number of rows in a table.
B. Filters rows based on a condition.
C. Calculates the sum of values in a column.
D. Assigns a unique sequential number to each row in the result set.

Solution

  1. Step 1: Understand the purpose of ROW_NUMBER()

    The ROW_NUMBER() function assigns a unique sequential integer to rows in the result set, starting at 1.

  2. Step 2: Compare with other options

    Counting rows, summing values, or filtering rows are done by other SQL functions like COUNT(), SUM(), or WHERE clause, not ROW_NUMBER().

  3. Final Answer:

    Assigns a unique sequential number to each row in the result set. -> Option D

  4. Quick Check:

    ROW_NUMBER() = unique row numbers [OK]
Hint: ROW_NUMBER() gives each row a unique number starting at 1 [OK]
Common Mistakes:
  • Confusing ROW_NUMBER() with COUNT()
  • Thinking ROW_NUMBER() filters rows
  • Assuming ROW_NUMBER() sums values
2. Which of the following is the correct syntax to assign row numbers ordered by salary in descending order?
easy
A. SELECT ROW_NUMBER() FROM employees ORDER BY salary DESC;
B. SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;
C. SELECT ROW_NUMBER() ORDER BY salary DESC FROM employees;
D. SELECT ROW_NUMBER(salary DESC) FROM employees;

Solution

  1. Step 1: Identify correct ROW_NUMBER() syntax

    The correct syntax uses ROW_NUMBER() OVER (ORDER BY column) to assign numbers ordered by a column.

  2. Step 2: Check each option

    Only SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; correctly uses OVER (ORDER BY salary DESC). The other options omit the OVER clause, pass arguments directly to ROW_NUMBER(), or misplace the ORDER BY clause.

  3. Final Answer:

    SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; -> Option B

  4. Quick Check:

    ROW_NUMBER() needs OVER with ORDER BY [OK]
Hint: ROW_NUMBER() must use OVER() with ORDER BY inside [OK]
Common Mistakes:
  • Omitting OVER() clause
  • Placing ORDER BY outside OVER()
  • Passing column directly to ROW_NUMBER()
3. Given the table sales with columns region and amount, what will this query return?
SELECT region, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales;
medium
A. The total count of sales per region.
B. A unique row number for all sales ignoring region, ordered by amount ascending.
C. A unique row number for each sale, restarting at 1 for each region, ordered by highest amount first.
D. The sum of amounts per region.

Solution

  1. Step 1: Understand PARTITION BY effect

    Using PARTITION BY region restarts the row numbering for each region group separately.

  2. Step 2: Understand ORDER BY inside ROW_NUMBER()

    Ordering by amount DESC means the highest amount in each region gets row number 1, next highest 2, and so on.

  3. Final Answer:

    A unique row number for each sale, restarting at 1 for each region, ordered by highest amount first. -> Option C

  4. Quick Check:

    ROW_NUMBER() with PARTITION BY = numbering per group [OK]
Hint: PARTITION BY restarts numbering per group [OK]
Common Mistakes:
  • Ignoring PARTITION BY and numbering all rows globally
  • Confusing ORDER BY direction
  • Thinking ROW_NUMBER() counts rows
4. Identify the error in this query:
SELECT name, ROW_NUMBER() ORDER BY salary FROM employees;
medium
A. Missing OVER() clause after ROW_NUMBER().
B. ORDER BY cannot be used in SELECT clause.
C. ROW_NUMBER() cannot be used without GROUP BY.
D. salary column does not exist.

Solution

  1. Step 1: Check ROW_NUMBER() syntax

    The ROW_NUMBER() function requires an OVER() clause specifying ordering or partitioning.

  2. Step 2: Analyze the query

    The query uses ROW_NUMBER() without OVER(), which causes a syntax error. ORDER BY inside SELECT is invalid without OVER().

  3. Final Answer:

    Missing OVER() clause after ROW_NUMBER(). -> Option A

  4. Quick Check:

    ROW_NUMBER() must have OVER() [OK]
Hint: ROW_NUMBER() always needs OVER() clause [OK]
Common Mistakes:
  • Forgetting OVER() after ROW_NUMBER()
  • Placing ORDER BY outside OVER()
  • Assuming ROW_NUMBER() works without ordering
5. You want to select the top 2 highest paid employees per department from the employees table. Which query correctly uses ROW_NUMBER() to achieve this?
hard
A. SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2;
B. SELECT *, ROW_NUMBER() FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department);
C. SELECT department, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees WHERE rn <= 2;
D. SELECT department, salary FROM employees ORDER BY salary DESC LIMIT 2;

Solution

  1. Step 1: Use ROW_NUMBER() with PARTITION BY department

    To get top 2 per department, assign row numbers restarting for each department ordered by salary descending.

  2. Step 2: Filter rows with row number less than or equal to 2

    Wrap the query as a subquery and filter where rn <= 2 to get top 2 employees per department.

  3. Final Answer:

    SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2; -> Option A

  4. Quick Check:

    ROW_NUMBER() + PARTITION + filter = top N per group [OK]
Hint: Use ROW_NUMBER() with PARTITION BY and filter rn <= N [OK]
Common Mistakes:
  • Not using PARTITION BY to group by department
  • Filtering on rn in the same SELECT without subquery
  • Using LIMIT without partitioning
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard