面試現場!月薪3w+的這些資料探勘SQL面試題你都掌握了嗎?

ShowMeAI 發表於 2022-11-23
面試 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面試題你都掌握了嗎?

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

面試現場!月薪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 <= order_date)
SELECT customer_id,
       product_name,
       order_date
FROM   view_tab
WHERE  first_order = 1 

查詢結果如下:

面試現場!月薪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 <= 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; 

查詢結果如下:

面試現場!月薪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 <= 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; 

查詢結果如下:

面試現場!月薪3w+的這些資料探勘SQL面試題你都掌握了嗎?

參考資料

面試現場!月薪3w+的這些資料探勘SQL面試題你都掌握了嗎?