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 |