关于sql:面试现场月薪3w的这些数据挖掘SQL面试题你都掌握了吗

作者：韩信子@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_salesFROM   sales       JOIN menu         ON sales.product_id = menu.product_idGROUP  BY sales.customer_id

查问后果如下：

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

咱们晓得，每位顾客每次光顾，都会生成 sales 中的相干记录，咱们能够基customer_id统计客户拜访餐厅的不同日期。

SELECT customer_id,       Count(DISTINCT( order_date )) as no_of_days_customer_visitedFROM   salesGROUP  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_nameFROM   view_tabWHERE  ranking = 1GROUP  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_purchsedFROM   sales       JOIN menu         ON sales.product_id = menu.product_idGROUP  BY sales.product_idORDER  BY most_purchsed DESCLIMIT  1

查问后果如下：

第2小问是问所有顾客在这个最热门的菜高低单的次数，咱们在上述SQL的根底上加上customer_id进行统计。

SELECT customer_id,       product_name,       Count(customer_id) AS purchase_countFROM   sales       JOIN menu         ON sales.product_id = menu.product_idWHERE  sales.product_id = 3GROUP  BY customer_idORDER  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_itemFROM   view_tabWHERE  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_dateFROM   view_tabWHERE  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_nameFROM   rankWHERE  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_salesFROM   sales       JOIN menu         ON sales.product_id = menu.product_id       JOIN members         ON sales.customer_id = members.customer_idWHERE  join_date > order_dateGROUP  BY sales.customer_idORDER  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_pointsFROM   view_tabGROUP  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 PointsFROM   dates D       JOIN sales S         ON D.customer_id = S.customer_id       JOIN menu M         ON M.product_id = S.product_idWHERE  S.order_date < d.last_dateGROUP  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_memberFROM   sales s       LEFT JOIN menu m              ON s.product_id = m.product_id       LEFT JOIN members mb              ON mb.customer_id = s.customer_idORDER  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 ranksFROM   joined_table;

查问后果如下：

参考资料

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