Task
You are given customer purchase data from Apptio Cost Transparency departments. Write a PostgreSQL query that returns each department's customers ranked by total spend within that department. Use a window function to rank customers after aggregating their spend, and include ties in the ranking. Only include completed purchases, treat NULL amounts as 0 during aggregation, and return the department name, customer name, total spend, and spend rank.
Schema
| Table | Column | Type | Description |
|---|
| departments | department_id | INT | Department identifier |
| departments | department_name | VARCHAR(100) | Department name |
| customers | customer_id | INT | Customer identifier |
| customers | customer_name | VARCHAR(100) | Customer name |
| customers | department_id | INT | Customer's department |
| purchases | purchase_id | INT | Purchase identifier |
| purchases | customer_id | INT | Purchasing customer |
| purchases | amount | DECIMAL(10,2) | Purchase amount |
| purchases | status | VARCHAR(20) | Purchase status |
| purchases | purchase_date | DATE | Purchase date |
Sample data
| department_name | customer_name | amount | status |
|---|
| FinOps | Alice Chen | 300.00 | completed |
| FinOps | Alice Chen | 200.00 | pending |
| FinOps | Bob Smith | 300.00 | completed |
| CloudOps | Diana Park | 500.00 | completed |
| CloudOps | Evan Lee | NULL | completed |
| | | |
Expected output
| department_name | customer_name | total_spend | spend_rank |
|---|
| CloudOps | Diana Park | 500.00 | 1 |
| CloudOps | Evan Lee | 0.00 | 2 |
| FinOps | Alice Chen | 300.00 | 1 |
| FinOps | Bob Smith | 300.00 | 1 |
| FinOps | Carla Gomez | 50.00 | 3 |
