Write a PostgreSQL query that ranks customers by total completed spend within each Apptio Cost Transparency department. Include customers with no completed purchases, treat NULL amounts as 0, and return department_name, customer_name, total_spend, and spend_rank.
| Column | Type | Description |
|---|---|---|
| department_id | ||
| department_name |
| Column | Type | Description |
|---|---|---|
| customer_id | ||
| customer_name | ||
| department_id |
| Column | Type | Description |
|---|---|---|
| purchase_id | ||
| customer_id | ||
| amount | ||
| status | ||
| purchase_date |
| 101 | Alice Chen | 1 |
| 105 | Evan Lee | 3 |
| 1001 | 101 | 300 | completed | 2024-02-10 |
| 1008 | 105 | completed | 2024-02-03 |
| 1 | FinOps |
| 3 | CloudOps |
One row per customer with a non-null departmentRanking resets for each departmentTied spend values share the same rankCustomers without completed purchases should still appear with 0 spend