Challenge
Consider two tables: Salespersons and Sales.
The Salespersons table has columns: id (int), name (varchar).
The Sales table has columns: salesperson_id (int), product_id (int), quantity (int), and sale_date (date).
Write a SQL query to identify the salesperson with the highest total quantity sold for each month.
DB Fiddle Instructions
Click ‘Edit on DB Fiddle’ to be taken to the in browser SQL playground where you can run your queries.
Explanation
First, we create a CTE MonthlySales to calculate the total quantity sold by each salesperson for each month. Then, we create another CTE RankSales to rank salespersons in each month by their total quantity sold in descending order. In the main query, we select the salespersons with the highest rank (1) in each month.