Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Challenge: Using Window Function | Some Additional Topics
Advanced Techniques in SQL
course content

Зміст курсу

Advanced Techniques in SQL

Advanced Techniques in SQL

1. ACID
Course OverviewWhat is ACID?AtomicityChallenge: Transaction CreationConsistencyIsolation. Read Committed and Uncommitted LevelsIsolation. Repeatable Read and Serializable LevelsChallenge: Specifying Isolation Level for Transaction
2. Query optimization.Indexes
What are IndexesB-tree IndexingChallenge: Creating B-tree IndexHash IndexingData CachingGeneral Optimization StrategiesChallenge: Query Optimization
3. Some Additional Topics
TriggersChallenge: Creating After TriggerWindow FunctionsTypes of Window FunctionsChallenge: Using Window FunctionData Control LanguageViewsNoSQL

Challenge: Using Window Function

Let's consider the bank_accounts table we have used in the previous sections:

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% and 50% will belong to the third class;
  • A person whose account balance is between 50% and 75% 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.

Note

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.

Завдання

Your task is to apply NTILE() function to get indormation about class of each account holder.

You have to:

  1. Specify number of classes as the argument of the function.
  2. Provide ordering by account balance to determine class.
  3. As we assume, that the first class is the class with the highest balance and so on, we have to order balances in descending order.

Завдання

Your task is to apply NTILE() function to get indormation about class of each account holder.

You have to:

  1. Specify number of classes as the argument of the function.
  2. Provide ordering by account balance to determine class.
  3. As we assume, that the first class is the class with the highest balance and so on, we have to order balances in descending order.

Все було зрозуміло?

Секція 3. Розділ 5
toggle bottom row

Challenge: Using Window Function

Let's consider the bank_accounts table we have used in the previous sections:

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% and 50% will belong to the third class;
  • A person whose account balance is between 50% and 75% 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.

Note

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.

Завдання

Your task is to apply NTILE() function to get indormation about class of each account holder.

You have to:

  1. Specify number of classes as the argument of the function.
  2. Provide ordering by account balance to determine class.
  3. As we assume, that the first class is the class with the highest balance and so on, we have to order balances in descending order.

Завдання

Your task is to apply NTILE() function to get indormation about class of each account holder.

You have to:

  1. Specify number of classes as the argument of the function.
  2. Provide ordering by account balance to determine class.
  3. As we assume, that the first class is the class with the highest balance and so on, we have to order balances in descending order.

Все було зрозуміло?

Секція 3. Розділ 5
toggle bottom row

Challenge: Using Window Function

Let's consider the bank_accounts table we have used in the previous sections:

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% and 50% will belong to the third class;
  • A person whose account balance is between 50% and 75% 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.

Note

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.

Завдання

Your task is to apply NTILE() function to get indormation about class of each account holder.

You have to:

  1. Specify number of classes as the argument of the function.
  2. Provide ordering by account balance to determine class.
  3. As we assume, that the first class is the class with the highest balance and so on, we have to order balances in descending order.

Завдання

Your task is to apply NTILE() function to get indormation about class of each account holder.

You have to:

  1. Specify number of classes as the argument of the function.
  2. Provide ordering by account balance to determine class.
  3. As we assume, that the first class is the class with the highest balance and so on, we have to order balances in descending order.

Все було зрозуміло?

Let's consider the bank_accounts table we have used in the previous sections:

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% and 50% will belong to the third class;
  • A person whose account balance is between 50% and 75% 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.

Note

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.

Завдання

Your task is to apply NTILE() function to get indormation about class of each account holder.

You have to:

  1. Specify number of classes as the argument of the function.
  2. Provide ordering by account balance to determine class.
  3. As we assume, that the first class is the class with the highest balance and so on, we have to order balances in descending order.

Секція 3. Розділ 5
Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантів
We're sorry to hear that something went wrong. What happened?
some-alt