【小练习】SQL_CASE

CASE

1. 我们想要根据相关的购买量了解三组不同的客户。最高的一组是终身价值（所有订单的总销售额）大于 200,000 美元的客户。第二组是在 200,000 到 100,000 美元之间的客户。最低的一组是低于 under 100,000 美元的客户。请提供一个表格，其中包含与每个客户相关的级别。你应该提供客户的名称、所有订单的总销售额和级别。消费最高的客户列在最上面。

   SELECT a.name, SUM(total_amt_usd) total_spent,   为什么不是SUM(o.total_amt_usd) ???
        CASE WHEN SUM(total_amt_usd) > 200000 THEN 'top'
        WHEN  SUM(total_amt_usd) > 100000 THEN 'middle'
        ELSE 'low' END AS customer_level
   FROM orders o
   JOIN accounts a
   ON o.account_id = a.id 
   GROUP BY a.name
   ORDER BY 2 DESC;
   

2. 现在我们想要执行和第一个问题相似的计算过程，但是我们想要获取在 2016 年和 2017 年客户的总消费数额。级别和上一个问题保持一样。消费最高的客户列在最上面。

   SELECT a.name, SUM(total_amt_usd) total_spent, 
        CASE WHEN SUM(total_amt_usd) > 200000 THEN 'top'
        WHEN  SUM(total_amt_usd) > 100000 THEN 'middle'
        ELSE 'low' END AS customer_level
   FROM orders o
   JOIN accounts a
   ON o.account_id = a.id
   WHERE occurred_at > '2015-12-31' 
   GROUP BY 1
   ORDER BY 2 DESC;
   

3. 我们想要找出绩效最高的销售代表，也就是有超过 200 个订单的销售代表。创建一个包含以下列的表格：销售代表名称、订单总量和标为 top 或 not 的列（取决于是否拥有超过 200 个订单）。销售量最高的销售代表列在最上面。

   SELECT s.name, COUNT(*) num_ords,
        CASE WHEN COUNT(*) > 200 THEN 'top'
        ELSE 'not' END AS sales_rep_level
   FROM orders o
   JOIN accounts a
   ON o.account_id = a.id 
   JOIN sales_reps s
   ON s.id = a.sales_rep_id
   GROUP BY s.name
   ORDER BY 2 DESC;
   

   值得注意的是，上述语句假定每个名称是唯一的，好几次都是这么假定的。否则需要根据名称和 ID 拆分表格。
   
   
4. 之前的问题没有考虑中间水平的销售代表或销售额。管理层决定也要看看这些数据。我们想要找出绩效很高的销售代表，也就是有超过 200 个订单或总销售额超过 750000 美元的销售代表。中间级别是指有超过 150 个订单或销售额超过 500000 美元的销售代表。创建一个包含以下列的表格：销售代表名称、总订单量、所有订单的总销售额，以及标为 top、middle 或 low 的列（取决于上述条件）。在最终表格中将销售额最高的销售代表列在最上面。

   SELECT s.name, COUNT(*), SUM(o.total_amt_usd) total_spent, 
        CASE WHEN COUNT(*) > 200 OR SUM(o.total_amt_usd) > 750000 THEN 'top'
        WHEN COUNT(*) > 150 OR SUM(o.total_amt_usd) > 500000 THEN 'middle'
        ELSE 'low' END AS sales_rep_level
   FROM orders o
   JOIN accounts a
   ON o.account_id = a.id 
   JOIN sales_reps s
   ON s.id = a.sales_rep_id
   GROUP BY s.name
   ORDER BY 3 DESC;
   

   根据上述标准，你可能会见到几个表现很差的销售代表！

另：