Challenge: Top Sales per Region
To solve this task, you need to identify the highest sale amount and the corresponding employee for each region in the sales table. You will use the ROW_NUMBER() window function to achieve this efficiently.
Step-by-step approach
-
Assign a row number within each region:
- Use
ROW_NUMBER()as a window function to assign a unique number to each sale within its region. - Partition the data by the
regioncolumn so that numbering restarts for each region. - Order the rows within each region by the
amountcolumn in descending order; this places the highest sale at the top for each region.
- Use
-
Select only the top sale per region:
- After assigning row numbers, the row with
ROW_NUMBER() = 1in each region will represent the highest sale for that region. - Use a subquery or common table expression (CTE) to filter and keep only those rows where the row number is 1.
- After assigning row numbers, the row with
-
Return the required columns:
- Select the
region,employee_id, andamountcolumns for the top sale in each region.
- Select the
Swipe to start coding
Write a query to find the highest sale amount and corresponding employee for each region using window functions.
- Assign a row number to each sale within its region, ordering by the sale amount in descending order.
- Select the region, employee ID, and sale amount for the row with the highest sale in each region.
Рішення
Дякуємо за ваш відгук!
single
Запитати АІ
Запитати АІ
Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат
Чудово!
Completion показник покращився до 4.55Challenge: Top Sales per Region
Свайпніть щоб показати меню
To solve this task, you need to identify the highest sale amount and the corresponding employee for each region in the sales table. You will use the ROW_NUMBER() window function to achieve this efficiently.
Step-by-step approach
-
Assign a row number within each region:
- Use
ROW_NUMBER()as a window function to assign a unique number to each sale within its region. - Partition the data by the
regioncolumn so that numbering restarts for each region. - Order the rows within each region by the
amountcolumn in descending order; this places the highest sale at the top for each region.
- Use
-
Select only the top sale per region:
- After assigning row numbers, the row with
ROW_NUMBER() = 1in each region will represent the highest sale for that region. - Use a subquery or common table expression (CTE) to filter and keep only those rows where the row number is 1.
- After assigning row numbers, the row with
-
Return the required columns:
- Select the
region,employee_id, andamountcolumns for the top sale in each region.
- Select the
Swipe to start coding
Write a query to find the highest sale amount and corresponding employee for each region using window functions.
- Assign a row number to each sale within its region, ordering by the sale amount in descending order.
- Select the region, employee ID, and sale amount for the row with the highest sale in each region.
Рішення
Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантівДякуємо за ваш відгук!
single
