Problem
Write a PostgreSQL query to find the top three customers by total spend in the last completed quarter by joining customers and transactions. Return customer_id, customer_name, and total_spend, ordered by highest spend first. Assume CURRENT_DATE = '2024-08-15', so the target quarter is 2024-04-01 to 2024-06-30.
Schema
| Column | Type | Description |
|---|---|---|
| customer_id INT PK | ||
| customer_name VARCHAR(100) | ||
| segment VARCHAR(50) | ||
| onboarded_at DATE |
| Column | Type | Description |
|---|---|---|
| transaction_id INT PK | ||
| customer_id INT FK | ||
| transaction_date DATE | ||
| amount DECIMAL(10,2) | ||
| cost_center VARCHAR(50) |
Representative Rows
customers
| 101 | Acme Health | Enterprise | 2023-11-10 |
| 102 | BluePeak Retail | Mid-Market | 2024-01-05 |
| 104 | Delta Manufacturing | Enterprise | 2023-09-15 |
transactions
| 1002 | 101 | 2024-04-01 | 300.00 | SaaS |
| 1006 | 102 | 2024-05-18 | 1200.00 | SaaS |
| 1010 | 104 | 2024-06-25 | 1600.00 | Labor |
Practicing as: Customer Success Engineer interview at ApptioHi, I'll play your Apptio interviewer for the Customer Success Engineer role. Candidates describe these interviews as often stressful and moderately difficult, so expect me to be direct and to the point. 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.
