利潤

使用 SQL 分析商業資料

Michel Semaan

Data Scientist

重點回顧

營收：公司賺進的金額
- 將每道餐點的價格乘以訂購數量，再把結果加總
成本：公司支出的金額
- 將每道餐點的成本乘以庫存數量，再把結果加總
利潤 = 營收 - 成本

為何利潤重要？

關鍵績效指標（KPI）：公司用來衡量表現的指標
每位使用者利潤：找出「最佳」使用者
每道餐點利潤：找出最賺錢的餐點
每月利潤：追蹤利潤隨時間的變化

營收 vs. 利潤

meal_id  meal_price  order_quantity  revenue  cost  profit
-------  ----------  --------------  -------  ----  ------
21       8           100             800      500   300
22       5           80              400      80    320

餐點 ID 21 有較高的價格（8）、訂購數量（100）與營收（800）
不過，餐點 ID 22 為 Delivr 帶來更高利潤（320）

合併營收與成本

查詢

WITH revenue AS (
  SELECT
    meals.meal_id,
    SUM(meal_price * meal_quantity) AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY meals.meal_id),

  cost AS (
  SELECT
    meals.meal_id,
    SUM(meal_cost * stocked_quantity) AS cost
  FROM meals
  JOIN stock ON meals.meal_id = stock.meal_id
  GROUP BY meals.meal_id)

計算利潤

查詢

WITH revenue AS (...),
  cost AS (...)

SELECT
  revenue.meal_id,
  revenue,
  cost,
  revenue - cost AS profit
FROM revenue
JOIN cost ON revenue.meal_id = cost.meal_id
ORDER BY profit DESC
LIMIT 3;

結果

meal_id  revenue   cost    profit    
-------  --------  ------  -------
11       17664.0   3072    14592.0   
10       16769.5   4573.5  12196.0   
8        13995.0   2332.5  11662.5

利潤

使用 SQL 分析商業資料