Challenge: Using Window Function
Let's assume we want to add information to the table about which class each user belongs to based on their account balance.
The classes will be determined as follows:
- A person whose account balance is in the bottom
25%will belong to the forth class; - A person whose account balance is between
25%and50%will belong to the third class; - A person whose account balance is between
50%and75%will belong to the second class; - A person whose account balance is in the top
25%will belong to the first class.
To solve this problem, we can use a window function called NTILE().
The NTILE() function is a window function that divides the rows in an ordered partition into a specified number of roughly equal groups, or "tiles".
It assigns each row a group number, starting at 1, based on the group it falls into.
NTILE(num_buckets) OVER (
ORDER BY sort_column
)
In the example, we don't need to specify a PARTITION BY clause because we want to apply the NTILE() function to the entire result set, not to specific subsets or partitions of the data.
The ORDER BY clause is sufficient as it defines the order in which the rows are distributed into tiles, ensuring that the grouping is based on the sorted order of the salaries across the entire table.
If we wanted to calculate tiles within specific groups (e.g., departments), we would use PARTITION BY.
Swipe to start coding
Fill in the blanks to use the NTILE() function so that each account holder is assigned a class from 1 to 4, where class 1 has the highest balances.
- Use 4 as the number of classes;
- Order by the
balancecolumn in descending order; - Make sure your result shows
account_holder,balance, and theirclass.
Solution
Thanks for your feedback!
single
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
Awesome!
Completion rate improved to 4.55Challenge: Using Window Function
Swipe to show menu
Let's assume we want to add information to the table about which class each user belongs to based on their account balance.
The classes will be determined as follows:
- A person whose account balance is in the bottom
25%will belong to the forth class; - A person whose account balance is between
25%and50%will belong to the third class; - A person whose account balance is between
50%and75%will belong to the second class; - A person whose account balance is in the top
25%will belong to the first class.
To solve this problem, we can use a window function called NTILE().
The NTILE() function is a window function that divides the rows in an ordered partition into a specified number of roughly equal groups, or "tiles".
It assigns each row a group number, starting at 1, based on the group it falls into.
NTILE(num_buckets) OVER (
ORDER BY sort_column
)
In the example, we don't need to specify a PARTITION BY clause because we want to apply the NTILE() function to the entire result set, not to specific subsets or partitions of the data.
The ORDER BY clause is sufficient as it defines the order in which the rows are distributed into tiles, ensuring that the grouping is based on the sorted order of the salaries across the entire table.
If we wanted to calculate tiles within specific groups (e.g., departments), we would use PARTITION BY.
Swipe to start coding
Fill in the blanks to use the NTILE() function so that each account holder is assigned a class from 1 to 4, where class 1 has the highest balances.
- Use 4 as the number of classes;
- Order by the
balancecolumn in descending order; - Make sure your result shows
account_holder,balance, and theirclass.
Solution
Switch to desktop for real-world practiceContinue from where you are using one of the options belowThanks for your feedback!
single
