🪣 NTILE() is a SQL window function that divides the rows in a result set into a specified number of approximately equal groups (buckets). Each row is assigned a bucket number starting from 1.

📖 What is NTILE()?

NTILE() distributes rows into a given number of groups based on the ordering defined in the OVER() clause. It is commonly used for percentile analysis, quartiles, deciles, and segmenting ranked data.

🎯 Why Use NTILE()?

NTILE() is useful when you need to divide data into equal-sized groups for reporting and analysis.

📋 Sample Table

📝 Basic Syntax

SELECT
    column1,
    column2,
    NTILE(number_of_groups) OVER (
        ORDER BY column_name
    ) AS Bucket
FROM table_name;

The argument passed to NTILE() specifies how many buckets the result set should be divided into.

💡 Example: Divide Employees into Four Salary Groups

SELECT
    EmployeeName,
    Salary,
    NTILE(4) OVER (
        ORDER BY Salary DESC
    ) AS SalaryQuartile
FROM Employees;

📊 Example Output

💡 Example: Divide Data into Three Groups

SELECT
    EmployeeName,
    Salary,
    NTILE(3) OVER (
        ORDER BY Salary DESC
    ) AS SalaryGroup
FROM Employees;

Because eight rows cannot be divided evenly into three groups, the earlier buckets receive one additional row.

💡 Example: Partition by Department

Use PARTITION BY to divide employees into buckets separately within each department.

SELECT
    EmployeeName,
    Department,
    Salary,
    NTILE(2) OVER (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS SalaryGroup
FROM Employees;

📊 Bucket Distribution Example

⚖️ NTILE() vs Other Ranking Functions

💼 Real-World Applications

🗄️ Database Compatibility

⚠️ Common Mistakes

⚠️ Best Practices

🚀 Key Points to Remember