NTILE

The NTILE() window function returns grouping of approximately equals rows of data into buckets numbered numerically. It is a useful function to put data into seperate buckets based on specifications provided on the OVER() clause. We determine the number of buckets by passing an integer value into the NTILE() function.

NTILE by Example

Suppose we have a table with employees data include employee_id and salary and we are interested in bucketing the information into similar ranges of compensation. We can use the NTILE() to achieve this, grouping similar salaries into buckets

Here is an example implementation:

SELECT
    employee_id,
    last_name,
    salary,
    NTILE(10) OVER(ORDER BY salary DESC) as salary_buckets
FROM employees
WHERE is_active = TRUE and salary IS NOT NULL;

OUTPUT employee_id | last_name | salary | salary_buckets -------------+------------+----------+---------------- 13 | Gonzalez | 98000.00 | 1 3 | Brown | 95000.00 | 1 35 | Peterson | 94000.00 | 1 45 | Wood | 92000.00 | 1 19 | Evans | 91000.00 | 1 7 | Taylor | 89000.00 | 2 30 | Richardson | 88000.00 | 2 43 | Price | 87000.00 | 2 24 | Morris | 86000.00 | 2 38 | James | 85000.00 | 2 16 | Phillips | 83000.00 | 3 50 | Perry | 83000.00 | 3 2 | Johnson | 82000.00 | 3 27 | Bailey | 81000.00 | 3 33 | Ward | 79000.00 | 4 41 | Kelly | 78000.00 | 4 9 | Garcia | 77000.00 | 4 26 | Cook | 76000.00 | 4 1 | Smith | 75000.00 | 5 47 | Ross | 75000.00 | 5 21 | Collins | 74000.00 | 5 31 | Cox | 73000.00 | 5 10 | Rodriguez | 72000.00 | 6 37 | Ramirez | 71000.00 | 6 5 | Wilson | 71000.00 | 6 17 | Campbell | 70000.00 | 6 4 | Davis | 68000.00 | 7 46 | Barnes | 68000.00 | 7 18 | Parker | 67000.00 | 7 36 | Gray | 67000.00 | 7 49 | Jenkins | 66000.00 | 8 28 | Rivera | 65000.00 | 8 8 | Martinez | 64000.00 | 8 25 | Reed | 63000.00 | 8 34 | Torres | 62000.00 | 9 14 | Perez | 61000.00 | 9 44 | Bennett | 61000.00 | 9 39 | Watson | 60000.00 | 9 6 | Anderson | 59000.00 | 10 29 | Cooper | 59000.00 | 10 20 | Edwards | 58000.00 | 10 15 | Turner | 55000.00 | 10 (42 rows)

Observing the buckets, you can naturally see the query returns a combintion of employees whose compensation largely are in the same category. This can be taken step further by adding a partition by clause by department for example. Notice that the input to NTILE() is an arbitrary number and therefore some thought should always be used to do this sort of bucketing.