Your question is Compare Profitability Across Segments. Start with the requirements and the four 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 transaction-level financial data and need to compare profitability across products, regions, and customer cohorts. Write a PostgreSQL query that returns the monthly gross profit, gross margin, and rank for each segment type, so you can identify the most profitable segments over time.
| Column | Type | Description |
|---|---|---|
| transaction_idPK | BIGINT | Unique transaction identifier |
| transaction_date | DATE | Date the transaction occurred |
| customer_id | BIGINT | Customer who made the transaction |
| product_id | INT | Product sold |
| region_id | INT | Region where the transaction was booked |
| revenue_amount | NUMERIC(12,2) | Revenue recognized for the transaction |
| cost_amount | NUMERIC(12,2) | Direct cost for the transaction |
| status | VARCHAR(20) | Transaction status |
| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Product identifier |
| product_name | VARCHAR(100) | Product name |
| product_category | VARCHAR(50) | Product category |
| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Region identifier |
| region_name | VARCHAR(50) | Region name |
| Column | Type | Description |
|---|---|---|
| customer_idPK | BIGINT | Customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| signup_date | DATE | Customer signup date |