Refactor a SQL Query for Efficient Employee Sales Aggregation
Improve a provided SQL query that calculates total sales per employee by refactoring it for better readability and optimization, while preserving its original behavior and output.
Challenge prompt
Given the following SQL query that calculates total sales amount per employee from sales and employees tables, refactor the query to improve readability and optimize performance without changing the output results. Your refactored query should: - Avoid unnecessary subqueries or joins - Use proper aggregation and filtering clauses - Maintain the original output (employee_id, employee_name, total_sales) Tables: - employees(employee_id, employee_name) - sales(sale_id, employee_id, sale_amount) Original query: SELECT e.employee_id, e.employee_name, ( SELECT SUM(sale_amount) FROM sales s WHERE s.employee_id = e.employee_id ) AS total_sales FROM employees e WHERE e.employee_id IN ( SELECT DISTINCT employee_id FROM sales ) ORDER BY total_sales DESC;
Guidance
- • Consider rewriting the query using JOIN and GROUP BY to reduce nested subqueries.
- • Ensure that employees without sales are excluded in the refactored query.
- • Focus on simplifying the query structure while preserving the original result set.
Hints
- • Use an INNER JOIN between employees and sales tables to directly access sales per employee.
- • Aggregate sales amounts using GROUP BY employee_id and employee_name.
- • Ordering by aggregated sales amount in descending order helps verify the refactored query outputs remain consistent.
Starter code
SELECT e.employee_id, e.employee_name, (
SELECT SUM(sale_amount)
FROM sales s
WHERE s.employee_id = e.employee_id
) AS total_sales
FROM employees e
WHERE e.employee_id IN (
SELECT DISTINCT employee_id FROM sales
)
ORDER BY total_sales DESC;Expected output
Columns: employee_id | employee_name | total_sales Rows: One row per employee who has at least one sale, sorted by total_sales descending
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.