Week 33 - SQL Chips and Chunks Newsletter

1. SQL Chip: Understanding Window Functions NTILE()

The NTILE() function is a powerful window function that divides the rows of an ordered partition into a specified number of approximately equal groups, or tiles. It's perfect for calculating performance quartiles (4 tiles), deciles (10 tiles), or any equal grouping across your dataset. Unlike GROUP BY, NTILE() assigns a tile number to each individual row while still showing the underlying data.

2. SQL Chunk: Dividing Funds into Quartiles

To rank our mutual funds into performance quartiles, we use NTILE(4). We order the funds by their Return_12_Months in descending order (best performance gets the lower tile number, though we can easily flip this).

For this example, let's imagine a table named Mutual_Funds with columns Fund_ID, Fund_Name, and Return_12_Months.

SELECT
    Fund_Name,
    Return_12_Months,
    NTILE(4) OVER (ORDER BY Return_12_Months DESC) AS Performance_Quartile
FROM
    Mutual_Funds
ORDER BY
    Return_12_Months DESC;

3. SQL Challenge: Counting Funds in the Top Quartile

Once we've assigned the quartile number, we can use a Common Table Expression (CTE) to easily filter and count the funds in the top quartile (where the Performance_Quartile is 1). This is how we provide the final count needed for product management.

WITH Ranked_Funds AS (
    SELECT
        Fund_Name,
        Return_12_Months,
        NTILE(4) OVER (ORDER BY Return_12_Months DESC) AS Performance_Quartile
    FROM
        Mutual_Funds
)
SELECT
    COUNT(Fund_Name) AS Funds_In_Top_Quartile
FROM
    Ranked_Funds
WHERE
    Performance_Quartile = 1;

4. SQL Mistake: Misusing NTILE() Without ORDER BY

A common mistake is trying to use a window function like NTILE() without an ORDER BY clause inside the OVER() part.

Incorrect Example (Missing ORDER BY):

-- This will not work correctly or predictably!
SELECT
    Fund_Name,
    NTILE(4) OVER () AS Performance_Quartile  -- Missing ORDER BY!
FROM
    Mutual_Funds;

Why it's a mistake: NTILE() requires a way to rank the data before dividing it into groups. Without ORDER BY, the function doesn't know how to group the funds by performance, leading to random or undefined quartile assignments. Always include ORDER BY inside the OVER() clause for ranking functions.

5. SQL in Practice: Finance - Portfolio Management

In Finance and Portfolio Management, ranking tools like NTILE() are essential for evaluating investment products. For our bank's proprietary mutual funds, we need to not only see the quartile but also count funds in the bottom quartile (Quartile 4) as a risk indicator. Funds consistently in the bottom quartile might be candidates for deeper review or retirement.

WITH Fund_Quartiles AS (
    SELECT
        Fund_Name,
        Return_12_Months,
        NTILE(4) OVER (ORDER BY Return_12_Months DESC) AS Performance_Quartile
    FROM
        Mutual_Funds
)
SELECT
    Performance_Quartile,
    COUNT(Fund_Name) AS Number_of_Funds
FROM
    Fund_Quartiles
WHERE
    Performance_Quartile IN (1, 4) -- Focus on top (1) and bottom (4)
GROUP BY
    Performance_Quartile
ORDER BY
    Performance_Quartile;

6. SQL Resource: MySQL NTILE Video Tutorial

Resource Name: MySQL 27: Window functions in SQL- Part I [RANK, DENSE_RANK and NTILE] - YouTube

UBS Asset Management utilizes rigorous portfolio analytics to assess mutual fund performance against benchmarks, helping to categorize products into distinct quartiles.

This structured evaluation ensures client alignment and fiduciary standards, leveraging detailed ranking metrics like NTILE to maintain sustainable investment offerings.

This video tutorial specifically covers Window Functions in MySQL 8.0, with a focus on ranking functions like NTILE(). Seeing the code executed live and the results instantly demonstrated is highly beneficial for understanding how NTILE divides rows and assigns buckets, making the abstract concept of ranking more concrete for visual learners.

_____________________________________________________________________

P.S. Looking for a gift for a fellow data analyst? Our new "Data Rules" tee is perfect for the programmer who knows clean SQL is the only way to operate