Your question is Break Down Revenue by Business Line. 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.
You are given Acumen transaction data and a product mapping table. Write a PostgreSQL query that breaks down performance by business line, returning each business line’s total revenue, transaction count, and average revenue per transaction. Any transaction whose product has a null or missing business line should be grouped into an Unmapped bucket.
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| product_id | INT | Product sold in the transaction |
| transaction_date | DATE | Transaction date |
| revenue | NUMERIC(12,2) | Revenue recognized on the transaction |
| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Product identifier |
| product_name | VARCHAR(100) | Product name |
| business_line | VARCHAR(50) | Business line assigned to the product |