ROW_NUMBER and NTILE

ROW_NUMBER() and NTILE(n) are window functions that help analyze ordered data in SQL.

• ROW_NUMBER() assigns a unique sequence number to each row based on a defined order.
• NTILE(n) divides rows into n roughly equal-sized groups, useful for creating quartiles, deciles, etc.

Syntax

The basic syntax for ROW_NUMBER() and NTILE(n) is as follows:

ROW_NUMBER and NTILE syntax

SELECT 
  column1,
  ROW_NUMBER() OVER (ORDER BY column2) AS row_num,
  NTILE(n) OVER (ORDER BY column2) AS group_id
FROM table_name;

• OVER (ORDER BY ...) defines how the rows are sorted before numbering
• NTILE(n) creates n groups from the ordered rows

Example: Course Progress

We have user progress for two courses, SQL Basics and Python Intro.

Each user has a completion_rate from 0 to 100, and we want to rank them within each course by that value.

You can use ROW_NUMBER() and NTILE(4) to rank learners and assign them to quartiles:

Rank learners and group them into quartiles

SELECT name, final_score,
  ROW_NUMBER() OVER (ORDER BY final_score DESC) AS row_num,
  NTILE(4) OVER (ORDER BY final_score DESC) AS quartile
FROM course_progress;

Output:

name	final_score	row_num	quartile
Alex	98	1	1
Sara	95	2	1
Daniel	91	3	2
Mia	90	4	2
Emma	86	5	3
Noah	83	6	3
John	80	7	4
Leo	78	8	4

• ROW_NUMBER() gives each learner a rank based on their score
• NTILE(4) splits them evenly into four quartile groups

You can use ROW_NUMBER() to assign a unique sequence to each row, and NTILE(n) to create quantiles or rank-based buckets.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In