Profit per eatery
Delivr is renegotiating its contracts with its eateries. The higher the profit that an eatery generates, the higher the rate that Delivr is willing to pay this eatery for the bulk purchase of meals.
The Business Development team asks you to find out how much profit each eatery is generating to strengthen their negotiating positions.
Note: You don't need to GROUP BY
eatery in the final query. You've already grouped by eatery in the revenue and cost CTEs; all that's required is joining them to each other to get each eatery's revenue and cost in one row. Since revenue and cost take up one row each per eatery, there are no additional groupings to be made.
This exercise is part of the course
Analyzing Business Data in SQL
Exercise instructions
- Calculate revenue per eatery in the
revenue
CTE. - Calculate cost per eatery in the
cost
CTE. - Join the two CTEs and calculate profit per eatery.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH revenue AS (
-- Calculate revenue per eatery
SELECT ___,
___ AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY eatery),
cost AS (
-- Calculate cost per eatery
SELECT ___,
___ AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY eatery)
-- Calculate profit per eatery
SELECT ___,
___
FROM revenue
JOIN cost ON revenue.eatery = cost.eatery
ORDER BY profit DESC;