Task
You are given customer and transaction data from an Apptio-style spend reporting workflow. Write a PostgreSQL query to return the top three customers by total spend in the last completed quarter. Include each customer's ID, name, and total spend, and sort the results from highest to lowest spend.
Assume CURRENT_DATE is 2024-08-15, so the last completed quarter is 2024-04-01 through 2024-06-30. Only transactions in that date range should count. Customers without matching transactions in that quarter should not appear.
Schema
customers
| column | type | description |
|---|
| customer_id | INT | Primary key for the customer |
| customer_name | VARCHAR(100) | Customer name |
| segment | VARCHAR(50) | Customer segment |
| onboarded_at | DATE | Customer onboarding date |
transactions
| column | type | description |
|---|
| transaction_id | INT | Primary key for the transaction |
| customer_id | INT | References customers.customer_id |
| transaction_date | DATE | Date of the transaction |
| amount | DECIMAL(10,2) | Transaction amount |
| cost_center | VARCHAR(50) | Spend classification label |
Sample data
customers
| customer_id | customer_name | segment | onboarded_at |
|---|
| 101 | Acme Health | Enterprise | 2023-11-10 |
| 102 | BluePeak Retail | Mid-Market | 2024-01-05 |
| 103 | Cedar Labs | SMB | 2024-02-20 |
transactions
| transaction_id | customer_id | transaction_date | amount | cost_center |
|---|
| 1004 | 101 | 2024-04-12 | 700.00 | Cloud |
| 1006 | 102 | 2024-05-18 | 1200.00 | SaaS |
| 1010 | 104 | 2024-06-25 | 1600.00 | Labor |
Expected output
| customer_id | customer_name | total_spend |
|---|
| 104 | Delta Manufacturing | 2500.00 |
| 102 | BluePeak Retail | 2000.00 |
| 101 | Acme Health | 1500.00 |
