CommencerCommencer gratuitement

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.

Cet exercice fait partie du cours

Analyzing Business Data in SQL

Afficher le cours

Instructions

  • Calculate revenue per month in the revenue CTE.
  • Calculate cost per month in the cost CTE.
  • Join the two CTEs and calculate profit per month.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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;
Modifier et exécuter le code