Profit per month
After prioritizing and making deals with eateries by their overall profits, Alice wants to track Delivr profits per month to see how well it's doing. You're here to help.
You're provided with two CTEs. The first stores revenue and the second stores cost. To access revenue and cost in one query, the two CTEs are joined in the last query. From there, you can apply the formula for profit Profit = Revenue - Cost to calculate profit per month.
Remember that revenue is the sum of each meal's price times its order quantity, and that cost is the sum of each meal's cost times its stocked quantity.
This exercise is part of the course
Analyzing Business Data in SQL
Exercise instructions
- Calculate revenue per month in the
revenueCTE. - Calculate cost per month in the
costCTE. - Join the two CTEs and calculate profit per month.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Set up the revenue CTE
WITH revenue AS (
SELECT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
___ AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY delivr_month),
-- Set up the cost CTE
cost AS (
SELECT
DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month,
___ AS cost
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY delivr_month)
-- Calculate profit by joining the CTEs
SELECT
___,
___
FROM revenue
JOIN cost ON revenue.delivr_month = cost.delivr_month
ORDER BY revenue.delivr_month ASC;