what would be the appropriate script for the below?

Question

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

Answer 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

Answer 2

Thank you! I am using MySQL

Answer 3

How does the below look to you?

Answer 4

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;

Answer 5

Looks almost perfect, but the month should also be derived from the order_date.
Also the keyword for sorting is: ORDER BY
Well done ;)