Using NTILE() for Data Segmentation in SQL

Use NTILE() for Data Bucketing

Write a SQL query to divide employees into quartiles based on their salary using the NTILE() window function.

Solution:

-- Divide employees into quartiles based on salary.
SELECT 
    EmployeeID,
    Name,
    Salary,
    NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile
FROM Employees;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use the NTILE() function to divide employees into four equal groups (quartiles) based on their salary.

2. Key Components :

1. NTILE(4) : Divides rows into four groups (quartiles).
2. OVER (ORDER BY Salary DESC) : Specifies the ordering for bucketing (highest salaries first).

3. Why Use NTILE()? :

1. NTILE() is useful for segmenting data into buckets for analysis, such as identifying top performers or grouping data for reporting.

4. Real-World Application :

1. For example, in HR systems, you might use this query to group employees into salary tiers for compensation analysis.

Additional Notes:

• Ensure that the number of rows is divisible by the number of buckets; otherwise, some buckets may have more rows than others.
• Use this exercise to teach how to segment data for analytical purposes.

For more Practice: Solve these Related Problems:

• Write a SQL query to divide customers into deciles based on their total purchase amount using NTILE().
• Write a SQL query to segment students into five groups based on their test scores using NTILE().
• Write a SQL query to classify products into three price tiers using the NTILE() function.
• Write a SQL query to split employees into quartiles based on their years of service using NTILE().

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Query JSON Data.
Next SQL Exercise: Create a Non-Recursive Common Table Expression.