Lifted from: Timmy Kurian’s Linkedin

Problem:

Given a table of employee sales data, find the top 3 employees with the highest total sales for each month.

Sample Tables (CREATE + INSERT):

CREATE TABLE employee_sales (
 id INT,
 employee_name VARCHAR(255),
 month VARCHAR(255),
 sales INT
);
INSERT INTO employee_sales (id, employee_name, month, sales)
VALUES (1, 'John Smith', 'January', 5000),
 (2, 'Jane Doe', 'January', 6000),
 (3, 'Bob Johnson', 'January', 4000),
 (4, 'Sara Williams', 'January', 7000),
 (5, 'Mark Brown', 'February', 8000),
 (6, 'Emily Davis', 'February', 9000),
 (7, 'Mike Wilson', 'February', 7500),
 (8, 'Lisa Taylor', 'February', 6000);

Query:

SELECT month, employee_name, sales
FROM (
 SELECT month, employee_name, sales,
 ROW_NUMBER() OVER(PARTITION BY month ORDER BY sales DESC) AS sales_rank
 FROM employee_sales
) AS ranked_sales
WHERE sales_rank <= 3;

Explanation:

This solution uses a window function to rank the sales for each employee in each month. Then, it selects only the top 3 sales for each month. This is achieved by using the PARTITION BY clause to group the sales by month and the ROW_NUMBER function to assign a rank to each sales record within the month. The results are then filtered to only show the top 3 sales for each month. This can be useful for identifying top-performing employees in each month and tracking their progress over time.