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.