面試現場！月薪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_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

面試現場！月薪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。

參考資料

相關文章