CommencerCommencer gratuitement

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.

Cet exercice fait partie du cours

Analyzing Business Data in SQL

Afficher le cours

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.

Exercice interactif pratique

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