面试现场!月薪3w+的这些数据挖掘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查看更多精彩内容

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

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

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

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

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

💡 数据说明

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

  • sales
  • menu
  • members

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

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 表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');

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

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

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 表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');

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

💡 数据分析挖掘问题

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

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

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

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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

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

这个问题同样会涉及到 sales 和 menu 表,我们会用到 customer_idproduct_nameorder_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_idproduct_name

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

第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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

📌 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

查询结果如下:

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

参考资料

面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

Original: https://www.cnblogs.com/showmeai/p/16627827.html
Author: ShowMeAI
Title: 面试现场!月薪3w+的这些数据挖掘SQL面试题你都掌握了吗? ⛵

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/504918/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • mysql

    mysql 1.1数据库 关系型数据库:数据存储在硬盘上 [En] Relational database: the data is stored in the hard disk…

    数据库 2023年5月24日
    0160
  • 解决执行npm run dev 后报错 Mix: not found的问题

    开发环境:homestead 宿主机环境:windows10 在homestead虚拟机内运行npm run dev时出现错误信息:mix: not found (此时打开lara…

    数据库 2023年6月14日
    0147
  • 前端常用函数封装

    常用函数封装 获取某日期若干个工作日后的日期 * &#x53C2;&#x6570;: * time: [String] &#x7ED9;&#x5B9…

    数据库 2023年6月11日
    0155
  • 01-MySQL连接查询、聚合函数

    1、连接查询 1.1、左连接 以左表为基准进行查询,左表数据回全部显示出来 右表中如果匹配连接条件的数据则显示相应字段的数据,如果不匹配,则显示为NULL 1.2、右连接 以右表为…

    数据库 2023年6月16日
    0162
  • 内部类

    🐓内部类 可以将一个类定义在另一个类或方法中,这样的类称为内部类 将类定义在另一个类中成员的位置 public class Inner { // 定义在类内部 class Demo…

    数据库 2023年6月14日
    0120
  • MySQL视图

    视图 常见的数据库对象 视图概述 为什么使用视图 可以帮助我们使用表中的 部分数据,对其修改可以改变原来表中的值 可以简化查询 控制数据的访问(权限) 视图的理解 视图是一个本身没…

    数据库 2023年5月24日
    0114
  • Maven项目添加外来jar包

    本例: 是为了读取Excel文件并把数据录入数据库,从数据库读写数据 写入Excel文件 用到的jar包:mysql—-poi Original: https://ww…

    数据库 2023年6月9日
    0113
  • linux系统安装MySQL数据库安装保姆级教程及1045错误和2058问题解决

    使用Mysql的zip压缩包解压版,下载之后需进行一定的配置,才能使用它。下面对Mysql压缩包版的安装方法进行详细的描述,如有疑问或错误,望及时反馈。 首先,mysql的官方下载…

    数据库 2023年6月11日
    0163
  • 刘畊宏男孩女孩看过来!运动数据分析挖掘!⛵

    💡 作者:韩信子@ShowMeAI📘数据分析 ◉ 技能提升系列:https://www.showmeai.tech/tutorials/33📘AI 面试题库系列:https://w…

    数据库 2023年6月14日
    0109
  • Android Studio的初次认识

    Android的初试 一、认识Android Studio 在我们新建项目的时候,会遇到这样的一个窗口,首先我们认识一下这些都是什么,这样我们才能够更好的进行下一步的学习! 这里的…

    数据库 2023年6月11日
    0170
  • 【黄啊码】使用think-queue产生大量日志文件怎么办?

    有时候某些原因程序出错,会有大量日志生成,最好调整下日志,单独出来。在配置文件config/queue.php开头添加: use think\facade\Log;Log::ini…

    数据库 2023年6月16日
    0129
  • Python学习笔记(十一)– Django API RESTful

    (1)路由;(2)视图类(提供给用户访问相当于原来的视图函数);(3)序列化类(提供给视图类使用,把对象序列化成Json) 注意:使用rest_framework,需先 setti…

    数据库 2023年6月16日
    0149
  • 简单日期格式化的使用

    简单日期格式化的应用 简单日期格式化的使用 面试题: &#x6709;&#x4E00;&#x4E2A;&#x65F6;&#x95F4; 20…

    数据库 2023年6月16日
    0111
  • Dubbo源码(九)-服务调用过程

    前言 本文基于Dubbo2.6.x版本,中文注释版源码已上传github:xiaoguyu/dubbo 源码分析均基于官方Demo,路径:dubbo/dubbo-demo 如果没有…

    数据库 2023年6月11日
    0160
  • MySQL 基础

    MySQL 基础 SQL 介绍 SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统(RDBMS)。 SQL 的范围包括数据…

    数据库 2023年5月24日
    0139
  • DM变更表空间存放路径

    1、变更前置条件 (1)、数据库服务器提供dmdba用户权限 (2)、目标路径有足够的空间可以使用 (3)、数据库可以重启 2、变更实施过程 2.1、变更前备份变更表空间目录需要对…

    数据库 2023年6月11日
    0213
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球