SQL Tutorial : Cost and Common Table Expressions (CTEs)

Want to learn more? Take the full course at https://learn.datacamp.com/courses/analyzing-business-data-in-sql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- Now that you've practiced calculating revenue, let's move on to 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. 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. 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. 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. 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,