sqlintermediate10 minutes

Refactor SQL Query for Optimized Employee Sales Report

Improve the given SQL query for generating a monthly sales report by employee to increase readability and performance without changing the output.

Challenge prompt

You are provided with a SQL query that generates a monthly sales summary report showing each employee's total sales amount, the number of sales, and average sale value. The query works correctly but includes redundant JOINs and repetitive subqueries making it inefficient and harder to maintain. Refactor this query to optimize performance and improve readability while keeping the exact output identical.

Guidance

• Identify and eliminate redundant JOINs or repeated calculations.
• Use more efficient aggregate functions and grouping.
• Consider using Common Table Expressions (CTEs) if it improves clarity.

Hints

• Check if any tables are joined multiple times unnecessarily.
• Avoid recalculating aggregates inside SELECT when possible.

Starter code

SELECT e.employee_id, e.employee_name, COUNT(s.sale_id) AS total_sales, SUM(s.amount) AS total_amount, AVG(s.amount) AS average_sale
FROM employees e
JOIN sales s ON e.employee_id = s.employee_id
JOIN departments d ON d.department_id = e.department_id
JOIN sales s2 ON s2.employee_id = e.employee_id
WHERE EXTRACT(MONTH FROM s.sale_date) = 3
GROUP BY e.employee_id, e.employee_name, d.department_name
ORDER BY total_amount DESC;

Expected output

A table with columns employee_id, employee_name, total_sales, total_amount, and average_sale displaying monthly sales summary for March, sorted by total sales amount descending.

Core concepts

SQL joinsAggregate functionsQuery optimizationQuery refactoring

Challenge a Friend

Send this duel to someone else and see if they can solve it.