NTILE()

The NTILE() SQL function groups data into roughly equal groups like the SQL “GROUP BY” clause. However NTILE() is applicable to window functions. A number can be specified with the function which will display the data organized roughly across a specified number of groups.

Syntax

The ... used below is meant to indicate code before or after the NTILE() clause below. It’s not part of the actual syntax.

A number is required between the parenthesis.

An ORDER BY and a PARTITION BY clause is a good idea to include. It makes the data more organized. The ORDER BY SQL clause can show query results in ascending or descending order. They can be specified with ASC or DESC respectively.

  ...
  NTILE(number_of_groups_to_split_data_between) OVER (
    PARTITION BY column1_name
    ORDER BY column2_name DESC
  ) ...

Example

Let’s say there is a “basketball_points” table where the first 10 rows look like this:

Then running an NTILE query so the different players and their points can be seen across weeks.

  SELECT
  NTILE(4) OVER (
    PARTITION BY week
    ORDER BY total_points DESC
  ) AS 'quartile',
  player,
  week,
  total_points
  FROM basketball_points
  LIMIT 10;

Running the following query will show data from the “basketball_points” table organized roughly into 4 groups. They’re going to be partitioned by week. That means that the results will display each week as a contiguous group. Each week is also ordered by total_points in descending order. Then the result of the “ntile” operation is set as “quartile”. So, now this new column highlights where players place in one of four quartiles of total points scored for that specific week.

Finally, the “player”, “week” and “total_points” columns are selected. The last line of the query adds a “LIMIT” condition, so that only the first 10 rows are displayed.

That will yield the following output: