Fix a Bug in an SQL Query for Aggregated Sales Performance with Window Functions
Identify and fix the bug in a complex SQL query that attempts to calculate monthly sales performance rankings by salesperson, using window functions and joins.
Challenge prompt
You are given a broken SQL query that is intended to rank salespersons based on their total monthly sales. The query uses window functions over joined tables but currently returns incorrect ranks and missing sales totals for some salespersons. Your task is to identify and correct the bugs so the query correctly returns salesperson_id, month, total_sales, and rank within each month sorted by total_sales descending.
Guidance
- • Check if the joins between sales and salesperson tables are correct and do not create duplicates that distort aggregation.
- • Inspect the window function PARTITION BY logic to ensure rankings reset for each month properly.
- • Verify that aggregation sums total sales correctly before applying the rank function.
Hints
- • Look for misplaced GROUP BY clauses that can cause incorrect sums or rankings.
- • Confirm if the window function ORDER BY clause orders by the correct alias or aggregated column.
- • Review if any filtering conditions exclude salespersons or months incorrectly.
Starter code
SELECT s.salesperson_id, DATE_TRUNC('month', sa.sale_date) AS month,
SUM(sa.amount) AS total_sales,
RANK() OVER (PARTITION BY DATE_TRUNC('month', sa.sale_date) ORDER BY SUM(sa.amount) DESC) AS rank
FROM sales sa
JOIN salesperson s ON s.salesperson_id = sa.salesperson_id
GROUP BY s.salesperson_id, month
ORDER BY month, rank;Expected output
salesperson_id | month | total_sales | rank ---------------|-------------|-------------|------ 1 | 2024-01-01 | 125000 | 1 3 | 2024-01-01 | 98000 | 2 2 | 2024-01-01 | 86000 | 3 1 | 2024-02-01 | 140000 | 1 3 | 2024-02-01 | 79000 | 2 ... | ... | ... | ...
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.