Post
Ask Question

Understanding the NTILE Window Function in SQL

Introduction

In this article, we will learn about the workings of the NTILE window function. The NTILE function is a powerful window function in SQL that allows you to divide rows into a specified number of ranked groups or Buckets. This function is particularly useful for creating equally sized buckets of data, which can be beneficial for various analytical purposes such as creating percentiles, quartiles, or any other equal-sized groupings.

Working of NTILE

The NTILE function divides an ordered set of data into a specified number of groups, where each group has approximately the same number of rows. If the number of rows is not divisible by the number of groups, the function ensures that no two groups differ in size by more than one row.

Setting Up Our Data

CREATE TABLE phone_sales (
    id INT PRIMARY KEY,
    model VARCHAR(50),
    price DECIMAL(10, 2),
    units_sold INT
);

INSERT INTO phone_sales (id, model, price, units_sold) VALUES
(1, 'iPhone 13', 799.99, 1000),
(2, 'Samsung Galaxy S21', 699.99, 850),
(3, 'Google Pixel 6', 599.99, 600),
(4, 'OnePlus 9', 729.99, 450),
(5, 'Xiaomi Mi 11', 749.99, 700),
(6, 'Sony Xperia 1 III', 1199.99, 200),
(7, 'iPhone 13 Pro', 999.99, 900),
(8, 'Samsung Galaxy S21 Ultra', 1199.99, 500),
(9, 'Google Pixel 6 Pro', 899.99, 400),
(10, 'OnePlus 9 Pro', 969.99, 300);

Now that we have our data let's explore how to use the NTILE function.

Dividing Phones into Price Quartiles

Let's divide our phones into four price quartiles.

SELECT 
    model,
    price,
    NTILE(4) OVER (ORDER BY price) AS price_quartile
FROM 
    phone_sales
ORDER BY 
    price;

/*
Output:

model                  price    price_quartile
---------------------------------------------
Google Pixel 6         599.99   1
Samsung Galaxy S21     699.99   1
OnePlus 9              729.99   2
Xiaomi Mi 11           749.99   2
iPhone 13              799.99   3
Google Pixel 6 Pro     899.99   3
iPhone 13 Pro          999.99   4
OnePlus 9 Pro          969.99   4
Sony Xperia 1 III     1199.99   4
Samsung Galaxy S21    1199.99   4
*/

In the above example, we've used NTILE(4) to divide our phones into four groups based on their price. The function assigns each row a number from 1 to 4, representing which quartile it belongs to.

Creating Sales Performance Tiers

Now, let's divide our phones into three tiers based on units sold.

SELECT 
    model,
    units_sold,
    NTILE(3) OVER (ORDER BY units_sold DESC) AS sales_tier
FROM 
    phone_sales
ORDER BY 
    units_sold DESC;

/*Output:

model                  units_sold  sales_tier
---------------------------------------------
iPhone 13              1000        1
iPhone 13 Pro          900         1
Samsung Galaxy S21     850         1
Xiaomi Mi 11           700         2
Google Pixel 6         600         2
Samsung Galaxy S21     500         2
OnePlus 9              450         3
Google Pixel 6 Pro     400         3
OnePlus 9 Pro          300         3
Sony Xperia 1 III      200         3

*/

In the above example, we've used NTILE(3) to create three sales performance tiers. The phones are ordered by units sold in descending order and then divided into three approximately equal groups.

Uses of NTILE

The NTILE function is particularly useful in various analytical scenarios.

  1. Creating equal-sized buckets for data analysis
  2. Assigning percentile ranks
  3. Grouping data for reporting purposes
  4. Identifying top or bottom performers in a dataset

Summary

By understanding and utilizing the NTILE function, you can gain valuable insights from your data and create more meaningful groupings for analysis and reporting. While NTILE is a powerful tool, it's important to consider the distribution of your data and the number of tiles you're creating to ensure the results are meaningful for your specific use case.


Similar Articles