0
what would be the appropriate script for the below?
PROMPT: Assume that we have the following tables with sales data in 2019, with columns as indicated: Table Name: Sales Dimensions: country_id (Varchar2) order_id (Varchar2) order_date (Date) sales_id (Varchar2) sales_person (Varchar2) order_amount (Number) Table Name: Country_Mapping Dimensions: country_id (Varchar2) country (Varchar2) region (Varchar2) Write a query that returns the name of the sales person with the second highest deal size (by order dollar amount) for each region and month in 2019 region month sales_person order_amount
5 Réponses
+ 1
Sounds like a homework assignment :)
Give it a try and let me know where you are stuck.
Also which SQL database are you using, because the solution is different for mysql, mssql, oracle etc.
Steps:
Join the two tables, easy to see the common key is country_id
Group by salesperson and month
Filter to year
Sort by the summed amount descending
Take the second row of the result
0
Thank you! I am using MySQL
0
How does the below look to you?
0
Select region, month, sales_person, sum(order_amount) as total_order_amount from Sales
JOIN Country_Mapping
ON sales.country_id = country_mapping.country_id
Group by Sales_person, Month, Region
Where year(order_date) = 2019
Sort by total_order_amount desc Limit 1 Offset 1;
0
Looks almost perfect, but the month should also be derived from the order_date.
Also the keyword for sorting is: ORDER BY
Well done ;)