1. Profit
It's time to use CTEs to combine the revenue and cost queries to calculate profit.
Before doing that, though, let's recap.
2. Recap
Revenue is the money a company makes, which in Delivr's case is the money that its customers pay to order the meals. Calculate revenue by multiplying each meal's price by its ordered quantity, then sum the results.
Cost is the money a company spends, which in Delivr's case is the money it pays to eateries to stock their meals. Calculate cost by multiplying each meal's co by its ordered quantity, then sum the results.
Finally, profit is the difference between revenue and cost.
3. Why is profit important?
Let's see why profit is such an important metric for companies.
Profit, alongside revenue and cost, is a key performance indicator, or KPI. A KPI is a metric with some value that a company use to measure its performance. You'll learn about other KPIs throughout the course.
Profit tells you whether a company is making or losing money, after everything is accounted for. Profit per user helps you identify the "best" users, those who bring Delivr the most money.
Profit per meal identifies the most profitable meals.
Finally, profit per month tracks profit over time, signaling whether or not Delivr is getting better at making money.
Why isn't revenue used instead?
4. Revenue vs profit
Let's explore why through an example.
Meal 21 has a price of 8, and has been ordered 100 times, so its revenue is 8 * 100 = 800. Its total purchase cost is 500. Profit is the difference between revenue and cost, so its profit is 800 - 500 = 300.
Meal 22 has a price of 5 and has been ordered 80 times, so its revenue is 5 * 80 = 400. Its purchase cost is 80, so its profit is 400 - 80 = 320.
Even though meal ID 21 had a higher price and ordered quantity (and thus, higher revenue), meal ID 22 generated more profit, because the difference between its revenue and cost was higher than meal 21's difference.
5. Bringing revenue and cost together
To calculate profit, combine the revenue and cost queries by storing each of them in a CTE. Let's calculate profit per meal.
To start, write a query that calculates revenue per meal and store it in a CTE called revenue, summing each meal's price times its ordered quantity and grouping by meal ID.
Then, write a query that calculates cost per meal and store that in a CTE called cost, summing each meal's cost times its stocked quantity and grouping by meal ID.
If you're calculating profit per some other metric, like eatery or month, make sure to group by that metric in both CTEs, since you'll join the two CTEs on that metric's column later on.
6. Calculating profit
Now that you've combined the revenue and cost queries, join revenue to cost on meal ID.
Then, select the meal ID column from one of the CTEs, making sure to specify which CTE to avoid an ambiguous column reference error, since both CTEs have that column. Finally, subtract cost from revenue to calculate profit.
This particular query orders by profit in descending order and only returns the top 3 meals by profit.
7. Profit
You now have the tools to answer core business questions about profit. In the following exercises, you'll calculate profit by several metrics.