Profit margins for all combinations of sales channels by org
Question
Suppose you're looking to calculate the net profit margin for each combination of sales org and sales channel. You're given the following tables to work with:
Table: sales_org
| sales_org_id | sales_org |
|---|---|
| 1 | Small/Medium Business |
| 2 | Consumer |
| 3 | Enterprise |
Table: sales_channel
| channel_id | channel |
|---|---|
| 1 | Direct from Manufacturer |
| 2 | Retail |
| 3 | eCommerce |
| 4 | TV Shopping |
Table: sale_info
| channel_id | channel_cost | sales_org_id | sales_org_revenue |
|---|---|---|---|
| 1 | 700 | 2 | 1200 |
| 2 | 1200 | 1 | 1450 |
| 3 | 850 | 3 | 1000 |
| 4 | 920 | 2 | 940 |
| 2 | 500 | 3 | 600 |
Using the above tables, write a SQL query to return the following:
- Net profit margin (sales_org_revenue - channel_cost) for all combinations of sales_org and channel (e.g. you'll need to JOIN on both sales_org_id and channel_id)
- For those combinations that have no revenue or cost data, you're still asked to return the rows, but you can mark revenue, cost, and profit all as 0
Click here to view/query the tables in an interactive SQL fiddle.