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

29次阅读

共计 6375 个字符，预计需要花费 16 分钟才能阅读完成。

💡 作者：韩信子 @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 张表对应的实体关系图如下所示：

销售额表对应的建表与数据插入 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');

菜单表对应的建表与数据插入 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');

会员表对应的建表与数据插入 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');

这里的信息显然来源于 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

查问后果如下：

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

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

查问后果如下：

这个问题同样会波及到 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。

查问后果如下：

这里很显然是以『菜』为外围，因而咱们会基于 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

查问后果如下：

在这个问题中，咱们要对客户购买每种产品的次数进行排名，因而应用窗口函数 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

查问后果如下：

这个问题中波及到会员信息，咱们会须要所有 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

查问后果如下：

同上一个问题，咱们须要用到所有 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

查问后果如下：

要查问客户在成为会员之前购买的总商品和破费的金额，订单日期须要小于入会日期。将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

查问后果如下：

这个问题用到 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

查问后果如下：

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

查问后果如下：

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;

查问后果如下：

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

正文完

sql

发表至： sql

2022-11-05

0

关于sql:clickhouse-安装和远程登录开启

关于sql:数聚未来云上新库-亚马逊云科技峰会即将开播免费领取企业上云方案

关于sql:深度解析PolarDB数据库并行查询技术

关于sql:一文带你搞懂如何优化慢SQL

关于前端:体验原生的组件化开发

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

💡 数据阐明

📌 表 1：Sales

📌 表 2：menu

📌 表 3：members

💡 数据分析开掘问题

📌 1. 每位顾客在餐厅生产的总金额是多少？

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

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

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

📌 5. 每位顾客最喜爱的菜品别离是什么？

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

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

📌 8. 每位会员入会前的总生产我的项目和生产金额是多少？

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

📌 10. 在客户退出打算后的第一周（蕴含入会日期），寿司和其余所有商品都是 2 倍积分，这种状况下 1 月份完结后客户有多少积分？

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

📌 12. 对客户点菜菜品按工夫先后编码，辨别会员与非会员状态，非会员的菜品不计入程序编码，记为 NULL。

参考资料

Just My Socks（注册教程内含优惠码）

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

💡 数据阐明

📌 表 1：Sales

📌 表 2：menu

📌 表 3：members

💡 数据分析开掘问题

📌 1. 每位顾客在餐厅生产的总金额是多少？

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

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

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

📌 5. 每位顾客最喜爱的菜品别离是什么？

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

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

📌 8. 每位会员入会前的总生产我的项目和生产金额是多少？

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

📌 10. 在客户退出打算后的第一周（蕴含入会日期），寿司和其余所有商品都是 2 倍积分，这种状况下 1 月份完结后客户有多少积分？

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

📌 12. 对客户点菜菜品按工夫先后编码，辨别会员与非会员状态，非会员的菜品不计入程序编码，记为 NULL。

参考资料

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）