Problem
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.
Schema
departments
| Column | Type | Description |
|---|---|---|
| department_id | ||
| department_name |
customers
| Column | Type | Description |
|---|---|---|
| customer_id | ||
| customer_name | ||
| department_id |
purchases
| Column | Type | Description |
|---|---|---|
| purchase_id | ||
| customer_id | ||
| amount | ||
| status | ||
| purchase_date |
Representative Rows
customers
| 101 | Alice Chen | 1 |
| 105 | Evan Lee | 3 |
purchases
| 1001 | 101 | 300 | completed | 2024-02-10 |
| 1008 | 105 | completed | 2024-02-03 |
departments
| 1 | FinOps |
| 3 | CloudOps |
Practicing as: Financial Analyst interview at The Church of Jesus Christ of Latter-day SaintsHi, I'll play your The Church of Jesus Christ of Latter-day Saints interviewer for the Financial Analyst role. Candidates describe these interviews as mostly positive and moderately difficult, so expect me to be friendly and conversational. Take your time with the question above and answer like we're in the room.
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
