课件来自datacamp

profit=revenue-cost

1.Revenue per week

Get the revenue per week for each week in June and check whether there's any consistent growth in revenue.

SELECT DATE_TRUNC('week', order_date) :: DATE AS delivr_week,
       # Calculate revenue
       SUM(meal_price * order_quantity) AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
# Keep only the records in June 2018
WHERE DATE_TRUNC('month', order_date) = '2018-06-01'
GROUP BY delivr_week
ORDER BY delivr_week ASC;

DATE_TRUNC在postgreSQL才有的用法

2.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.

# Set up the revenue CTE
WITH revenue AS ( 
    SELECT
        DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
        sum(meal_price*order_quantity) 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,
        sum(meal_cost*stocked_quantity) AS cost
    FROM meals
    JOIN stock ON meals.meal_id = stock.meal_id
    GROUP BY delivr_month)
#Calculate profit by joining the CTEs
SELECT
    revenue.delivr_month,
    (revenue-cost) as profit
FROM revenue
JOIN cost ON revenue.delivr_month = cost.delivr_month
ORDER BY revenue.delivr_month ASC;