SQL CUME_DIST Tutorial 🎯

Welcome to the SQL CUME_DIST Tutorial! In this lesson, we'll learn about the CUME_DIST function, a powerful tool for ranking rows in SQL. Let's dive in! 🐳

What is CUME_DIST? 📝

The SQL CUME_DIST function returns the cumulative density of a row within a result set. In simpler terms, it calculates the rank of a row relative to other rows in the same group, taking into account the ordering specified by an ORDER BY clause.

CUME_DIST is particularly useful for analyzing percentiles, ranking, and scoring in various scenarios.

How CUME_DIST works 💡

The CUME_DIST function works by ordering the result set based on a specified expression.
For each row, CUME_DIST calculates the total number of rows that come before it, including ties, and divides it by the total number of rows in the result set.
The result is a value between 0 and 1, indicating the row's rank within the group.

Example 1: Simple CUME_DIST Example 📝

Let's consider a table of student scores:

sql

CREATE TABLE StudentScores (
    StudentID INT,
    Score INT
);

INSERT INTO StudentScores (StudentID, Score)
VALUES (1, 85), (2, 90), (3, 80), (4, 95), (5, 70), (6, 88), (7, 92), (8, 75), (9, 83), (10, 97);

To find the rank of each student using CUME_DIST, we can use the following query:

sql

SELECT
    StudentID,
    Score,
    CUME_DIST() OVER (ORDER BY Score DESC) AS Rank
FROM
    StudentScores;

This query will return the following result:

StudentID | Score | Rank
----------|-------|-----
        1 |    97 |  1.00
        2 |    95 |  0.95
        4 |    92 |  0.87
        7 |    90 |  0.78
        6 |    88 |  0.71
        3 |    85 |  0.64
        5 |    83 |  0.58
        9 |    80 |  0.46
        8 |    75 |  0.37
        10 |    70 |  0.29

As you can see, the Rank column represents the cumulative density of each student's score within the group.

Example 2: Real-world Application 📝

In a sports competition, let's say we want to find the rank of each team based on their total points. We can create a table for storing the team scores and use CUME_DIST to calculate the ranks.

sql

CREATE TABLE TeamScores (
    TeamID INT,
    Points INT
);

INSERT INTO TeamScores (TeamID, Points)
VALUES (1, 15), (2, 20), (3, 10), (4, 12), (5, 17), (6, 13), (7, 18), (8, 14), (9, 11), (10, 25);

To find the rank of each team using CUME_DIST, we can use the following query:

sql

SELECT
    TeamID,
    Points,
    CUME_DIST() OVER (ORDER BY Points DESC) AS Rank
FROM
    TeamScores;

This query will return the following result:

TeamID | Points | Rank
-------|--------|----
    10 |    25 |  1.00
     7 |    18 |  0.87
     5 |    17 |  0.75
     2 |    20 |  0.62
     6 |    13 |  0.50
     3 |    10 |  0.38
     9 |    11 |  0.25
     4 |    12 |  0.19
     8 |    14 |  0.13

Quiz 💡

That's it for the SQL CUME_DIST tutorial! I hope this lesson was helpful in understanding how to use the CUME_DIST function in SQL. 🤖🤯🚀

Remember to practice using CUME_DIST in various scenarios to reinforce your understanding of this powerful tool. Happy learning, and see you in the next lesson! 🎓🎉