Your question is Top Customers by Last-Quarter Spend. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| 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) |
| 101 | Acme Health | Enterprise | 2023-11-10 |
| 102 | BluePeak Retail | Mid-Market | 2024-01-05 |
| 104 | Delta Manufacturing | Enterprise | 2023-09-15 |
| 1002 | 101 | 2024-04-01 | 300.00 | SaaS |
| 1006 | 102 | 2024-05-18 | 1200.00 | SaaS |
| 1010 | 104 | 2024-06-25 | 1600.00 | Labor |