Profit margins for all combinations of sales channels by org


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 editor.


Access restricted

Subscribe to premium account to see the solution.

Get premium now