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

14th Nov 2019, 12:47 AM
Chad Bannan
Chad Bannan - avatar
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
14th Nov 2019, 5:08 AM
Tibor Santa
Tibor Santa - avatar
0
Thank you! I am using MySQL
14th Nov 2019, 2:49 PM
Chad Bannan
Chad Bannan - avatar
0
How does the below look to you?
14th Nov 2019, 2:49 PM
Chad Bannan
Chad Bannan - avatar
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;
14th Nov 2019, 2:50 PM
Chad Bannan
Chad Bannan - avatar
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 ;)
14th Nov 2019, 3:59 PM
Tibor Santa
Tibor Santa - avatar