delivr_app.sql

-- 1.What is the sum of all the amounts spent by user ID 17 on Delivr orders?

SELECT
	SUM(meal_price *  order_quantity) AS total_bill
FROM meals m
 INNER JOIN orders o
  ON m.meal_id = o.meal_id
WHERE user_id=17;

-- 2.What is the revenue per week in July 2022, and is there consistent growth observed throughout the month?

SELECT
	DATE_TRUNC('week', order_date) :: DATE AS delivr_week
	SUM(meal_price * order_quantity) AS revenue
FROM meals m
 INNER JOIN orders o
  ON m.meal_id = o.meal_id
WHERE DATE_TRUNC('month', order_date) :: DATE = '2022-07-01'
GROUP BY delivr_week
ORDER BY delivr_week;

-- 3.What is Delivr's total cost since it began operating?

WITH costs AS (
 SELECT
    meals.meal_id,
    (meal_cost * stocked_quantity) AS meal_cost
 FROM meals
  INNER JOIN stock
   ON meals.meal_id = stock.meal_id)

SELECT
    SUM(meal_cost) AS total_cost
FROM costs;

-- 4.What are the top 5 meals by overall cost that Delivr has spent the most on for stocking?

SELECT
	m.meal_id,
	SUM(meal_cost * stocked_quantity) AS meal_cost
FROM meals m
  INNER JOIN stock s
   ON m.meal_id = s.meal_id
GROUP BY m.meal_id
ORDER BY meal_cost DESC
LIMIT 5;

-- 5.What is the average monthly cost incurred by Delivr during its early months (before July 2022)?

WITH monthly_cost AS (
 SELECT
  	DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month,
  	SUM(meal_cost * stocked_quantity) AS cost
 FROM meals m
  INNER JOIN stock s
   ON m.meal_id = s.meal_id	
 GROUP BY delivr_month
 ORDER BY delivr_month)

SELECT
	ROUND(AVG(cost),2) AS avg_monthly_cost
FROM monthly_cost
WHERE delivr_month < '2022-07-01'

-- 6.What is the profit generated by each eatery  to strengthen Delivr's negotiating positions for contract renegotiation?

--revenue per eatery
WITH revenue AS (
 SELECT
	eatery,
	SUM(meal_price * order_quantity) AS revenue
 FROM meals m
  INNER JOIN orders o
   ON m.meal_id = o.meal_id
 GROUP BY eatery),
 
 -- cost per eatery
 cost AS (
 SELECT
	eatery,
	SUM(meal_price * stocked_quantity) AS cost
 FROM meals m
  INNER JOIN stock s
   ON m.meal_id = s.meal_id
 GROUP BY eatery)
 
 -- profit per eatery
 
SELECT
	r.eatery,
	(revenue - cost) AS profit
FROM revenue r
 INNER JOIN cost c
  ON r.eatery = c.eatery
ORDER BY profit DESC;

-- 7.What is the monthly profit generated by each of the top 7 eateries?

--revenue per eatery
WITH revenue AS (
 SELECT
	DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
	SUM(meal_price * order_quantity) AS revenue
 FROM meals m
  INNER JOIN orders o
   ON m.meal_id = o.meal_id
 GROUP BY delivr_month),
 
 -- cost per eatery
 cost AS (
 SELECT
	DATE_TRUNC('month', stocking_date) :: DATE AS delivr_month
	SUM(meal_price * stocked_quantity) AS cost
 FROM meals m
  INNER JOIN stock s
   ON m.meal_id = s.meal_id
 GROUP BY delivr_month)
 
 -- profit per eatery
SELECT
	r.delivr_month,
	(revenue - cost) AS profit
FROM revenue r
 INNER JOIN cost c
  ON r.delivr_month = c.delivr_month
ORDER BY r.delivr_month DESC
LIMIT 7;