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.

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) creates n groups from the ordered rows

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:

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() 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.