利润

使用 SQL 分析业务数据

Michel Semaan

Data Scientist

回顾

收入（Revenue）：公司获得的钱
- 将每份餐点的价格乘以其点单数量，再求和
成本（Cost）：公司支出的钱
- 将每份餐点的成本乘以其库存数量，再求和
利润 = 收入 - 成本

利润为何重要？

关键绩效指标（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 分析业务数据