Task
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.
Schema
| table | column | type | description |
|---|
transactions | transaction_id | BIGINT | Unique transaction identifier |
transactions | transaction_date | DATE | Date the transaction occurred |
transactions | customer_id | BIGINT | Customer who made the transaction |
transactions | product_id | INT | Product sold |
transactions | region_id | INT | Region where the transaction was booked |
transactions | revenue_amount | NUMERIC(12,2) | Revenue recognized for the transaction |
transactions | cost_amount | NUMERIC(12,2) | Direct cost for the transaction |
transactions | status | VARCHAR(20) | Transaction status |
products | product_id | INT | Product identifier |
products | product_name | VARCHAR(100) | Product name |
products | product_category | VARCHAR(50) | Product category |
regions | region_id | INT | Region identifier |
regions | region_name | VARCHAR(50) | Region name |
customers | customer_id | BIGINT | Customer identifier |
customers | customer_name | VARCHAR(100) | Customer name |
customers | signup_date | DATE | Customer signup date |
Sample data
transactions
| transaction_id | transaction_date | customer_id | product_id | region_id | revenue_amount | cost_amount | status |
|---|
| 1001 | 2024-01-05 | 1 | 10 | 1 | 500.00 | 320.00 | settled |
| 1002 | 2024-01-07 | 2 | 10 | 1 | 300.00 | 180.00 | settled |
| 1003 | 2024-01-09 | 3 | 11 | 2 | 250.00 | 140.00 | settled |
| 1004 | 2024-02-02 | 1 | 11 | 2 | 400.00 | 260.00 | settled |
| 1005 | 2024-02-11 | 4 | 12 | 1 | 0.00 | 15.00 | refunded |
| 1006 | 2024-02-15 | 5 | 12 | 3 | 700.00 | 410.00 | settled |
| 1007 | 2024-03-03 | 6 | 10 | 2 | 150.00 | 90.00 | settled |
| 1008 | 2024-03-18 | 2 | 13 | 3 | 600.00 | 390.00 | settled |
products
| product_id | product_name | product_category |
|---|
| 10 | Wallet Plus | Payments |
| 11 | Savings Boost | Deposits |
| 12 | Micro Loan | Lending |
| 13 | Bill Pay Pro | Payments |
regions
| region_id | region_name |
|---|
| 1 | North |
| 2 | West |
| 3 | South |
| 4 | East |
customers
| customer_id | customer_name | signup_date |
|---|
| 1 | Asha Mehta | 2023-11-12 |
| 2 | Ravi Kumar | 2023-12-03 |
| 3 | Neha Singh | 2024-01-14 |
| 4 | Omar Khan | 2024-01-20 |
| 5 | Priya Nair | 2024-02-08 |
| 6 | Kabir Das | 2024-02-25 |
Expected output
| segment_type | segment_name | month | gross_profit | gross_margin | profit_rank |
|---|
| product | Wallet Plus | 2024-01 | 300.00 | 0.3750 | 1 |
| product | Savings Boost | 2024-02 | 140.00 | 0.3500 | 2 |
| region | South | 2024-03 | 210.00 | 0.3500 | 1 |
| cohort | 2023-12 | 2024-01 | 120.00 | 0.4000 | 2 |
| cohort | 2024-01 | 2024-02 | 140.00 | 0.3500 | 1 |
