Data segmentation with Amazon Redshift’s NTILE(n) Function

Data segmentation is a critical aspect of data analysis, and Amazon Redshift offers a powerful tool to accomplish this task – the NTILE() function with practical examples. We will also dive into real-world use cases to demonstrate the function’s significance.

DDL Example: Creating a Sample Table

Let’s start by creating a sample table named freshers_in_webstats to illustrate the usage of the NTILE() function.

CREATE TABLE freshers_in_webstats (
    date DATE,
    pageviews INT,
    visitors INT
);

Our table includes three columns: date, pageviews, and visitors, which represent the date of the statistics, the number of pageviews, and the number of visitors, respectively.

DML Example: Populating Data

Now, let’s insert some sample data into our freshers_in_webstats table for demonstration purposes:

INSERT INTO freshers_in_webstats (date, pageviews, visitors)
VALUES
    ('2023-09-01', 1000, 500),
    ('2023-09-02', 1200, 600),
    ('2023-09-03', 1500, 750),
    ('2023-09-04', 800, 400),
    ('2023-09-05', 1300, 650);

With this data, we now have a dataset that spans five days of web statistics.

Understanding NTILE()

The NTILE() function is utilized to divide the result set into approximately equal parts or “buckets” and assigns a bucket number to each row. This function is particularly useful for quantile analysis and data segmentation. The formula for NTILE() is as follows:

NTILE(n) OVER (ORDER BY column_name)

Real-World Use Case: Web Traffic Analysis

Now, let’s explore a real-world use case where the NTILE() function can be invaluable. Imagine you are a data analyst at Freshers In, and you want to segment your website’s daily pageviews into quartiles to gain insights into user engagement levels.

Here’s how you can achieve this using the NTILE() function:

SELECT
    date,
    pageviews,
    NTILE(4) OVER (ORDER BY pageviews) AS pageviews_quartile
FROM
    freshers_in_webstats;

In this SQL query:

We select the date and pageviews columns.
We apply the NTILE() function with a parameter of 4 (to create quartiles) over the pageviews column, ordering the rows in ascending order based on pageviews.
We alias the result of NTILE() as pageviews_quartile.

Result Set:

    date       | pageviews | pageviews_quartile
--------------+-----------+---------------------
  2023-09-04  |   800     |         1
  2023-09-01  |   1000    |         2
  2023-09-02  |   1200    |         3
  2023-09-05  |   1300    |         3
  2023-09-03  |   1500    |         4

The pageviews_quartile column now shows the quartile each day’s pageviews fall into. This segmentation allows you to analyze user engagement levels more effectively. For instance, you can compare the performance of days falling into the first quartile (lowest engagement) with those in the fourth quartile (highest engagement).

Applications of NTILE()

The NTILE() function has several practical applications:

Segmentation for Analysis: It allows you to segment data into quantiles, quartiles, or any desired number of buckets for in-depth analysis.
Resource Allocation: In business, it can help in allocating resources or efforts based on the segmentation of customers, products, or regions.
Benchmarking: You can use NTILE() to compare performance across different segments, making it useful for benchmarking and goal setting.

Read more on Redshift
Read more on Hive
Read more on Snowflake