1. Cost and Common Table Expressions (CTEs)
Now that you've practiced calculating revenue, let's move on to cost.
2. Cost
Cost is the money a company spends to operate.
Some of Delivr’s costs are the salaries it pays to its employees, the acquisition and maintenance costs of its electric scooter delivery fleet, and the costs of the meals it offers. Let’s focus on the last one and explore the tables you need to calculate Delivr's meal costs.
3. Tables you'll need
You've already seen the meals table, which contains metadata on the meals Delivr offers.
The new table is stock. Each row represents a bulk purchase that Delivr made to stock the meals it offers, with the stocking date, the ID of the stocked meal, and how many of that meal were stocked.
4. Calculating cost
This query returns the top three meals by cost, displaying how much each cost in total. It's similar to the query calculating revenue, with some differences.
Instead of joining meals to orders, join meals to stock to get the meal price and the stocked quantity in the same query. Instead of summing each meal's price times its ordered quantity, sum each meal's cost times its stocked quantity, since cost is how much Delivr pays eateries to stock their meals.
Like with revenue, you can calculate cost per metrics other than meal_id, such as the year and the eatery.
5. How do you combine revenue and cost?
Recall that profit is the difference between revenue and cost. You've seen the queries for both revenue and cost now. How do you combine them to calculate profit?
Writing one query that combines the two turns out to be quite unwieldy. It would be much better to write the revenue query and the cost query separately, then somehow write a third query that joins one to the other.
6. Common Table Expressions (CTEs)
Enter Common Table Expressions.
A Common Table Expression, also known as a CTE, is a way to store a query's results in a temporary table, which is referenced in a following query.
To store a query's results in a CTE, wrap the query in parentheses, and precede it with WITH, then the name of the temporary table you want to store the query in, then AS.
If you want to store multiple queries, separate them with a comma, and ommit the WITH after the first CTE.
Finally, you can select from whatever queries you store in CTEs in the final query.
7. CTEs in action
Let's see CTEs in action.
The query within parentheses calculates each meal's total stocked quantities and stocking costs. Its results are stored in a CTE called costs_and_quantities.
In the final query, the meal ID, total stocked quantity, and total stocking costs of the top 3 meals by stocking costs are selected from costs_and_quantities as if it were any other table. Costs_and_quantites is deleted after this query finishes running.
8. Cost and Common Table Expressions (CTEs)
The query just covered could've been written without a CTE; however, the CTE made the query cleaner and easier to understand.
In the next lesson, you'll use CTEs to combine the revenue and cost queries, which you'll use to calculate profit.