? 作者：韩信子@ShowMeAI
? 数据分析实战系列：https://www.showmeai.tech/tutorials/40
? AI 面试题库系列：https://www.showmeai.tech/tutorials/48
? 本文地址：https://www.showmeai.tech/article-detail/318
? 声明：版权所有，转载请联系平台与作者并注明出处
? 收藏ShowMeAI查看更多精彩内容

本篇内容基于场景面试题完成，在给定场景和数据表的前提下，有一系列的分析挖掘问题，大家可以基于SQL来完成。

场景：Danny非常喜欢日本料理，因此在 2021 年初，他决定冒险冒险，开了一家可爱的小餐厅，出售他最喜欢的 3 种食物：寿司、咖喱和拉面。这家餐厅从其几个月的运营中获取了一些非常基本的数据，但不知道如何使用他们的数据来帮助他们经营业务。

Danny 想基于收集到的数据来更深入地了解他的客户，例如他们的就餐模式、点餐花费以及他们最喜欢哪些菜等。下面你就来帮助他完成核心问题的分析吧，这里的分析基于SQL完成。

对于SQL更详尽的内容，欢迎大家查阅ShowMeAI制作的速查手册，快学快用：

• 编程语言速查表 | SQL 速查表

? 数据说明

本次的场景涉及到3个核心数据集，都已存入数据库表中：

• sales
• menu
• members

这3张表对应的实体关系图如下所示：

? 表1：Sales

销售额表对应的建表与数据插入SQL语句如下：

CREATE TABLE sales (   "customer_id" VARCHAR(1),   "order_date" DATE,   "product_id" INTEGER );  INSERT INTO sales   ("customer_id", "order_date", "product_id") VALUES   ('A', '2021-01-01', '1'),   ('A', '2021-01-01', '2'),   ('A', '2021-01-07', '2'),   ('A', '2021-01-10', '3'),   ('A', '2021-01-11', '3'),   ('A', '2021-01-11', '3'),   ('B', '2021-01-01', '2'),   ('B', '2021-01-02', '2'),   ('B', '2021-01-04', '1'),   ('B', '2021-01-11', '1'),   ('B', '2021-01-16', '3'),   ('B', '2021-02-01', '3'),   ('C', '2021-01-01', '3'),   ('C', '2021-01-01', '3'),   ('C', '2021-01-07', '3'); 

? 表2：menu

菜单表对应的建表与数据插入SQL语句如下：

CREATE TABLE menu (   "product_id" INTEGER,   "product_name" VARCHAR(5),   "price" INTEGER );  INSERT INTO menu   ("product_id", "product_name", "price") VALUES   ('1', 'sushi', '10'),   ('2', 'curry', '15'),   ('3', 'ramen', '12'); 

? 表3：members

会员表对应的建表与数据插入SQL语句如下：

CREATE TABLE members (   "customer_id" VARCHAR(1),   "join_date" DATE );  INSERT INTO members   ("customer_id", "join_date") VALUES   ('A', '2021-01-07'),   ('B', '2021-01-09'); 

? 数据分析挖掘问题

? 1.每位顾客在餐厅消费的总金额是多少？

这里的信息显然来源于sales和menu两张表，我们先对它们进行关联，而问题中的『每位顾客』意味着我们会基于 customer_id 进行分组统计。最后的SQL如下所示：

SELECT customer_id,        Sum(price) AS total_sales FROM   sales        JOIN menu          ON sales.product_id = menu.product_id GROUP  BY sales.customer_id  

查询结果如下：

? 2.每位顾客光顾了餐厅多少天？

我们知道，每位顾客每次光顾，都会生成 sales 中的相关记录，我们可以基customer_id统计客户访问餐厅的不同日期。

SELECT customer_id,        Count(DISTINCT( order_date )) as no_of_days_customer_visited FROM   sales GROUP  BY customer_id  

查询结果如下：

? 3.每位顾客购买的菜单中的第一道菜是什么？

这个问题同样会涉及到 sales 和 menu 表，我们会用到customer_id、product_name、order_date字段，按照要求，我们希望查询每个客户从菜单中购买的第 1 件商品，因此使用 rank 函数进行订单日期排序。对应的SQL如下所示：

WITH view_tab      AS (SELECT customer_id,                 product_name,                 order_date,                 Rank()                   OVER(                     partition BY customer_id                     ORDER BY order_date ) AS Ranking          FROM   sales                 JOIN menu                   ON sales.product_id = menu.product_id) SELECT customer_id,        product_name FROM   view_tab WHERE  ranking = 1 GROUP  BY customer_id,           product_name 

我们这里启用了临时表view_tab，选择 ranking 位1的数据对应的customer_id和product_name。

查询结果如下：

? 4.菜单上购买最多的菜是什么，所有顾客购买了多少次？

这里很显然是以『菜』为核心，因此我们会基于product_id进行分组，同时我们需要统计的是购买了多少次，因此需要根据count(product_id)的结果进行排序，对应的SQL如下所示：

SELECT product_name,        Count(sales.product_id) AS most_purchsed FROM   sales        JOIN menu          ON sales.product_id = menu.product_id GROUP  BY sales.product_id ORDER  BY most_purchsed DESC LIMIT  1  

查询结果如下：

第2小问是问所有顾客在这个最热门的菜上下单的次数，我们在上述SQL的基础上加上customer_id进行统计。

SELECT customer_id,        product_name,        Count(customer_id) AS purchase_count FROM   sales        JOIN menu          ON sales.product_id = menu.product_id WHERE  sales.product_id = 3 GROUP  BY customer_id ORDER  BY purchase_count DESC  

查询结果如下：

? 5.每位顾客最喜欢的菜品分别是什么？

在这个问题中，我们要对客户购买每种产品的次数进行排名，因此使用窗口函数 rank，按customer_id划分，按客户购买产品的次数（计数）排序。对应的SQL如下：

WITH view_tab      AS (SELECT customer_id,                 product_name,                 Count(product_name)                    AS count_item,                 Rank()                   OVER(                     partition BY customer_id                     ORDER BY Count(product_name) DESC) AS most_popular          FROM   sales                 JOIN menu                   ON sales.product_id = menu.product_id          GROUP  BY customer_id,                    product_name) SELECT customer_id,        product_name,        count_item FROM   view_tab WHERE  most_popular = 1  

查询结果如下：

? 6.客户成为会员后最先购买的商品是什么？

这个问题中涉及到会员信息，我们会需要所有 3 个表，我们要把它们关联起来。我们要查询客户成为会员后购买的第一件商品，因此要选出订单日期需要大于加入日期的订单。使用窗口函数通过对customer_id进行划分并按order_date 对其进行排序，可以实现对第一个购买日期进行排序。这里依旧会需要借助临时表view_tab。最终的SQL如下：

WITH view_tab      AS (SELECT sales.customer_id,                 product_name,                 order_date,                 Rank()                   OVER(                     partition BY sales.customer_id                     ORDER BY order_date) AS first_order          FROM   sales                 JOIN menu                   ON sales.product_id = menu.product_id                 JOIN members                   ON sales.customer_id = members.customer_id          WHERE  join_date <= order_date) SELECT customer_id,        product_name,        order_date FROM   view_tab WHERE  first_order = 1  

查询结果如下：

? 7.在客户成为会员之前最后购买的是哪件菜品？

同上一个问题，我们需要用到所有 3 个表。要查询客户在成为会员之前购买的商品，订单日期需要小于加入日期。使用窗口函数通过对customer_id进行划分并按order_date对其进行排序，对第一个购买日期进行降序排列。最终的SQL如下：

WITH rank      AS (SELECT S.customer_id,                 M.product_name,                 Dense_rank()                   OVER (                     partition BY S.customer_id                     ORDER BY S.order_date) AS Rank          FROM   sales S                 JOIN menu M                   ON m.product_id = s.product_id                 JOIN members Mem                   ON Mem.customer_id = S.customer_id          WHERE  S.order_date < Mem.join_date) SELECT customer_id,        product_name FROM   rank WHERE  rank = 1  

查询结果如下：

? 8.每位会员入会前的总消费项目和消费金额是多少？

要查询客户在成为会员之前购买的总商品和花费的金额，订单日期需要小于入会日期。将customer_id 的计数命名为total_items，将消费金额price的总和命名为total_sales，最终的SQL如下：

SELECT sales.customer_id,        Count(sales.product_id) AS total_items,        Sum(price)              AS total_sales FROM   sales        JOIN menu          ON sales.product_id = menu.product_id        JOIN members          ON sales.customer_id = members.customer_id WHERE  join_date > order_date GROUP  BY sales.customer_id ORDER  BY sales.customer_id  

查询结果如下：

? 9.如果每消费 1 美元累计10积分，寿司消费有 2 倍积分——每位顾客会有多少积分？

这个问题用到sales和menu两张表。我们使用case语句将积分分配给客户购买的商品，并对积分进行统计求和得到每位顾客的积分数。对应的SQL如下：

WITH view_tab      AS (SELECT customer_id,                 CASE                   WHEN product_name = 'sushi' THEN price * 20                   ELSE price * 10                 END AS points          FROM   sales                 JOIN menu                   ON sales.product_id = menu.product_id) SELECT customer_id,        Sum(points) AS total_points FROM   view_tab GROUP  BY customer_id  

查询结果如下：

? 10.在客户加入计划后的第一周（包含入会日期），寿司和其他所有商品都是2倍积分，这种情况下1月份结束后客户有多少积分？

WITH dates      AS (SELECT *,                 Dateadd(day, 6, join_date) AS valid_date,                 Eomonth('2021-01-31')      AS last_date          FROM   members) SELECT S.customer_id,        Sum(CASE              WHEN m.product_id = 1 THEN m.price * 20              WHEN S.order_date BETWEEN D.join_date AND D.valid_date THEN              m.price * 20              ELSE m.price * 10            END) AS Points FROM   dates D        JOIN sales S          ON D.customer_id = S.customer_id        JOIN menu M          ON M.product_id = S.product_id WHERE  S.order_date < d.last_date GROUP  BY S.customer_id 

查询结果如下：

? 11.构建新的宽表，包含这些字段信息：customer_id, order_date, product_name, price, member [Y/N]

SELECT s.customer_id,        s.order_date,        m.product_name,        m.price,        CASE          WHEN mb.join_date > s.order_date THEN 'N'          WHEN mb.join_date <= s.order_date THEN 'Y'          ELSE 'N'        END AS is_member FROM   sales s        LEFT JOIN menu m               ON s.product_id = m.product_id        LEFT JOIN members mb               ON mb.customer_id = s.customer_id ORDER  BY s.customer_id;  

查询结果如下：

? 12.对客户点菜菜品按时间先后编码，区分会员与非会员状态，非会员的菜品不计入顺序编码，记为NULL。

WITH joined_table      AS (SELECT s.customer_id,                 s.order_date,                 m.product_name,                 m.price,                 CASE                   WHEN mb.join_date > s.order_date THEN 'N'                   WHEN mb.join_date <= s.order_date THEN '‘Y'                   ELSE 'N'                 END AS is_member          FROM   sales s                 LEFT JOIN menu m                        ON s.product_id = m.product_id                 LEFT JOIN members mb                        ON mb.customer_id = s.customer_id          ORDER  BY s.customer_id) SELECT *,        CASE          WHEN is_member = 'N' THEN NULL          ELSE Rank()                 OVER(                   partition BY customer_id, is_member                   ORDER BY order_date)        END AS ranks FROM   joined_table;  

查询结果如下：

参考资料

• ? 编程语言速查表 | SQL 速查表：https://www.showmeai.tech/article-detail/99