ROW_NUMBER and NTILE

ROW_NUMBER() and NTILE(n) are window functions used to analyze ordered data in SQL.

• ROW_NUMBER() assigns a unique number to each row according to the specified order.
• NTILE(n) divides rows into n nearly equal-sized groups, useful for quartiles, deciles, and other distributions.

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

Copy
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) distributes the ordered rows into n groups

Suppose we track learners’ final_score in a course and want to assign row numbers and split them into quartiles based on their scores.

You can use ROW_NUMBER() and NTILE(4) to assign row numbers and group learners into quartiles:

Copy
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;

• ROW_NUMBER() assigns a unique number to each row according to the specified order.
• NTILE(4) divides the rows into four nearly equal-sized quartile groups.

• ROW_NUMBER() is best when you need a strict sequence or to uniquely identify rows (e.g., pagination, deduplication).
• NTILE(n) is best when you want to segment data into groups for analysis (e.g., quartiles, deciles, performance tiers).