Đây là một case study giúp mình luyện tập kỹ năng làm sạch hay phân tích với SQL. Đây là một trong những thử thách của chuỗi 8weeksqlchallenge. Xem thêm tại: Link
Mặc dù đã có rất nhiều solution về case study này, đây sẽ là phiên bản xử lý và phân tích theo hiểu biết của chính mình.

Giới thiệu:

Danny muốn kết hợp Uber và pizza để tạo ra Pizza Runner. Bởi vì Danny đã có vài năm kinh nghiệm làm nhà khoa học dữ liệu - anh ấy nhận thức rất rõ rằng việc thu thập dữ liệu sẽ rất quan trọng đối với sự phát triển kinh doanh của anh ấy. Danny cần được hỗ trợ để làm sạch dữ liệu và áp dụng một số tính toán cơ bản để anh ấy có thể chỉ đạo người chạy tốt hơn và tối ưu hóa hoạt động của Pizza Runner.

ERD:

Danny đã thiết kế ERD - sơ đồ quan hệ thực thể như sau:

Họ đã cung cấp code để tạo dựng các bảng theo sơ đồ trên như sau:

CREATE SCHEMA pizza_runner;
SET search_path = pizza_runner;

DROP TABLE IF EXISTS runners;
CREATE TABLE runners (
  "runner_id" INTEGER,
  "registration_date" DATE
);
INSERT INTO runners
  ("runner_id", "registration_date")
VALUES
  (1, '2021-01-01'),
  (2, '2021-01-03'),
  (3, '2021-01-08'),
  (4, '2021-01-15');


DROP TABLE IF EXISTS customer_orders;
CREATE TABLE customer_orders (
  "order_id" INTEGER,
  "customer_id" INTEGER,
  "pizza_id" INTEGER,
  "exclusions" VARCHAR(4),
  "extras" VARCHAR(4),
  "order_time" TIMESTAMP
);

INSERT INTO customer_orders
  ("order_id", "customer_id", "pizza_id", "exclusions", "extras", "order_time")
VALUES
  ('1', '101', '1', '', '', '2020-01-01 18:05:02'),
  ('2', '101', '1', '', '', '2020-01-01 19:00:52'),
  ('3', '102', '1', '', '', '2020-01-02 23:51:23'),
  ('3', '102', '2', '', NULL, '2020-01-02 23:51:23'),
  ('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
  ('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
  ('4', '103', '2', '4', '', '2020-01-04 13:23:46'),
  ('5', '104', '1', 'null', '1', '2020-01-08 21:00:29'),
  ('6', '101', '2', 'null', 'null', '2020-01-08 21:03:13'),
  ('7', '105', '2', 'null', '1', '2020-01-08 21:20:29'),
  ('8', '102', '1', 'null', 'null', '2020-01-09 23:54:33'),
  ('9', '103', '1', '4', '1, 5', '2020-01-10 11:22:59'),
  ('10', '104', '1', 'null', 'null', '2020-01-11 18:34:49'),
  ('10', '104', '1', '2, 6', '1, 4', '2020-01-11 18:34:49');


DROP TABLE IF EXISTS runner_orders;
CREATE TABLE runner_orders (
  "order_id" INTEGER,
  "runner_id" INTEGER,
  "pickup_time" VARCHAR(19),
  "distance" VARCHAR(7),
  "duration" VARCHAR(10),
  "cancellation" VARCHAR(23)
);

INSERT INTO runner_orders
  ("order_id", "runner_id", "pickup_time", "distance", "duration", "cancellation")
VALUES
  ('1', '1', '2020-01-01 18:15:34', '20km', '32 minutes', ''),
  ('2', '1', '2020-01-01 19:10:54', '20km', '27 minutes', ''),
  ('3', '1', '2020-01-03 00:12:37', '13.4km', '20 mins', NULL),
  ('4', '2', '2020-01-04 13:53:03', '23.4', '40', NULL),
  ('5', '3', '2020-01-08 21:10:57', '10', '15', NULL),
  ('6', '3', 'null', 'null', 'null', 'Restaurant Cancellation'),
  ('7', '2', '2020-01-08 21:30:45', '25km', '25mins', 'null'),
  ('8', '2', '2020-01-10 00:15:02', '23.4 km', '15 minute', 'null'),
  ('9', '2', 'null', 'null', 'null', 'Customer Cancellation'),
  ('10', '1', '2020-01-11 18:50:20', '10km', '10minutes', 'null');


DROP TABLE IF EXISTS pizza_names;
CREATE TABLE pizza_names (
  "pizza_id" INTEGER,
  "pizza_name" TEXT
);
INSERT INTO pizza_names
  ("pizza_id", "pizza_name")
VALUES
  (1, 'Meatlovers'),
  (2, 'Vegetarian');


DROP TABLE IF EXISTS pizza_recipes;
CREATE TABLE pizza_recipes (
  "pizza_id" INTEGER,
  "toppings" TEXT
);
INSERT INTO pizza_recipes
  ("pizza_id", "toppings")
VALUES
  (1, '1, 2, 3, 4, 5, 6, 8, 10'),
  (2, '4, 6, 7, 9, 11, 12');

DROP TABLE IF EXISTS pizza_toppings;
CREATE TABLE pizza_toppings (
  "topping_id" INTEGER,
  "topping_name" TEXT
);
INSERT INTO pizza_toppings
  ("topping_id", "topping_name")
VALUES
  (1, 'Bacon'),
  (2, 'BBQ Sauce'),
  (3, 'Beef'),
  (4, 'Cheese'),
  (5, 'Chicken'),
  (6, 'Mushrooms'),
  (7, 'Onions'),
  (8, 'Pepperoni'),
  (9, 'Peppers'),
  (10, 'Salami'),
  (11, 'Tomatoes'),
  (12, 'Tomato Sauce');

Data Cleaning:

Vì dữ liệu được tạo ở các bảng phải cần được làm sạch để việc phân tích được chính xác. Trong các bảng có dữ liệu NULL nhưng được nhập tay null hay `` khó cho việc phân tích.
Mình đã làm sạch lại các bảng cho phù hợp và có thể phân tích chính xác cho các Key Question của challenge như sau:

--làm sạch bảng runner_orders
UPDATE runner_orders 
SET cancellation = NULL
WHERE cancellation LIKE 'null' OR cancellation LIKE '';

UPDATE runner_orders
SET duration = 
CASE 
        WHEN duration ~ '^[0-9]+[^0-9]' THEN CAST(NULLIF(SUBSTRING(duration FROM '^[0-9]+'), '') AS INTEGER)
        WHEN duration ~ '^[0-9]+$' THEN CAST(duration AS INTEGER)
        ELSE NULL
END
WHERE duration ~ '^[0-9]+(\D|$)';

UPDATE runner_orders 
SET duration = NULL
WHERE duration LIKE 'null';

UPDATE runner_orders 
SET pickup_time = NULL
WHERE pickup_time LIKE 'null';

UPDATE runner_orders
SET distance = 
    CASE 
        WHEN distance = 'null' THEN NULL
        WHEN distance ~ '[0-9]+(\.[0-9]+)?[a-zA-Z\s]*$' THEN 
        	CAST(SUBSTRING(distance FROM '.*?([0-9]+(\.[0-9]+)?)\s*[a-zA-Z\s]*$') AS DECIMAL)
        ELSE CAST(distance AS DECIMAL)
    END;
   
-- làm sạch bảng customer_orders
UPDATE customer_orders  
SET extras  = NULL
WHERE extras LIKE 'null' OR extras LIKE '';

UPDATE customer_orders  
SET exclusions = NULL
WHERE exclusions LIKE 'null' OR exclusions LIKE '';

Data Cleaning:

Pizza Metrics:

Có bao nhiêu pizza đã được đặt?

SELECT COUNT(*) order_counts
FROM customer_orders

Có bao nhiêu đơn đặt hàng khác nhau đã được đặt?

SELECT COUNT(DISTINCT order_id) total_orders
FROM customer_orders

Với mỗi runner, bao nhiêu đơn đặt hàng đã được giao thành công?

SELECT COUNT(order_id) - COUNT(cancellation) AS total_success_orders
FROM runner_orders

Với mỗi customer, bao nhiêu pizza loại ‘Vegetarian’ và ‘Meatlovers’ đã được đặt?

SELECT 
    c.customer_id
    ,SUM(CASE WHEN pizza_name = 'Vegetarian' THEN 1 ELSE 0 END) AS total_vegetarian
    ,SUM(CASE WHEN pizza_name = 'Meatlovers' THEN 1 ELSE 0 END) AS total_meatlovers
FROM customer_orders c
LEFT JOIN pizza_names p ON c.pizza_id = p.pizza_id 
GROUP BY 1
ORDER BY 1

Số lượng pizza tối đa được giao của một đơn hàng là bao nhiêu?

SELECT 
  order_id
  ,total_orders max_order
FROM (SELECT 
    order_id
    ,COUNT(*) total_orders
    ,DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
FROM customer_orders 
GROUP BY order_id) a 
WHERE a.rank = 1

Khối lượng đơn đặt hàng mỗi ngày trong tuần là bao nhiêu?

SELECT 
  TO_CHAR(order_time, 'Day') day_of_week
  ,COUNT(*) total_orders
FROM customer_orders
GROUP BY 1

Runner and Customer Experience

Có bao nhiêu runners đăng ký mỗi tuần? (tuần bắt đầu 2021-01-01)

SELECT 
 TO_CHAR(registration_date, 'W') week
  ,COUNT(*) total_runners
FROM runners 
GROUP BY 1
ORDER BY 1

Thời gian trung bình tính bằng phút để mỗi runner đến trụ sở Pizza Runner để nhận đơn hàng là bao nhiêu?

SELECT 
	r.runner_id
	,ROUND(AVG(EXTRACT(EPOCH FROM (CAST(r.pickup_time AS TIMESTAMP) 
	- CAST(c.order_time AS TIMESTAMP))) / 60), 2) avg_time
FROM runner_orders r
JOIN customer_orders c
ON r.order_id = c.order_id
GROUP BY 1
ORDER BY 1

Với mỗi customer, quãng đường trung bình cần phải đi là bao nhiêu?

SELECT 
	c.customer_id 
	,ROUND(AVG(r.distance::numeric), 2) avg_distance
FROM customer_orders c
JOIN runner_orders r
ON c.order_id = r.order_id 
GROUP BY 1
ORDER BY 1

Sự chênh lệch giữa thời gian giao hàng lâu nhất và ngắn nhất cho tất cả các đơn hàng là bao nhiêu?

SELECT 
    MAX(CAST(duration AS INTEGER)) - MIN(CAST(duration AS INTEGER)) duration_diff
FROM runner_orders;

11.Tốc độ trung bình của mỗi runner trong mỗi lần giao hàng là bao nhiêu?

SELECT 
  runner_id
  ,order_id 
  ,ROUND(AVG(CAST(distance AS DECIMAL) / (CAST(duration AS DECIMAL) / 60)), 2) avg_velocity
FROM runner_orders
WHERE cancellation IS NULL
GROUP BY 1,2
ORDER BY 1,2

12.Tỷ lệ phần trăm giao hàng thành công của mỗi runner là bao nhiêu?

SELECT 
  DISTINCT runner_id 
  ,(((COUNT(*) OVER (PARTITION BY runner_id) - COUNT(cancellation) OVER (PARTITION BY runner_id))::numeric)*100) / (COUNT(*) OVER (PARTITION BY runner_id)) percent_sucess_order
FROM runner_orders

Ingredient Optimisation

13.Các thành phần tiêu chuẩn cho mỗi pizza là gì?

--tạo bảng tạm để chứa thông tin tách các thành phần thành từng dòng
CREATE TEMP TABLE temp_pizza_recipes AS
SELECT
    pr.pizza_id,
    ut.topping_id
FROM
    pizza_recipes pr
CROSS JOIN LATERAL (
    SELECT UNNEST(STRING_TO_ARRAY(pr.toppings, ', '))::int AS topping_id
) AS ut;

--xóa đi bảng cũ 
DELETE FROM pizza_recipes;
--insert nội dung từ bảng mới vào
INSERT INTO pizza_recipes (pizza_id, toppings)
SELECT * FROM temp_pizza_recipes;
--xóa bảng tạm
DROP TABLE temp_pizza_recipes;
--đổi tên cột: toppings->topping_id
ALTER TABLE pizza_recipes RENAME COLUMN toppings TO topping_id;

--câu truy vấn
SELECT 
  pizza_name
  ,STRING_AGG(t.topping_name, ', ') ingredients 
FROM pizza_recipes r
JOIN pizza_toppings t
ON r.topping_id::text = t.topping_id::text
JOIN pizza_names n 
ON r.pizza_id = n.pizza_id 
GROUP BY 1
ORDER BY 1

14.Topping nào thường được thêm vào nhất?

SELECT
  t.topping_name
  ,COUNT(order_id) AS order_count
FROM (
    SELECT
        order_id,
        UNNEST(STRING_TO_ARRAY(extras, ', ')::int[]) AS extra
    FROM
        customer_orders 
) AS temp
JOIN pizza_toppings t ON temp.extra = t.topping_id
GROUP BY 1
ORDER BY 1
LIMIT 1;

15.Topping nào thường bị loại ra nhất?

SELECT
  t.topping_name
  ,COUNT(order_id) AS order_count
FROM (
    SELECT
        order_id,
        UNNEST(STRING_TO_ARRAY(exclusions, ', ')::int[]) AS exclusion
    FROM
        customer_orders 
) AS temp
JOIN pizza_toppings t ON temp.exclusion = t.topping_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

16.Tổng số lượng của từng thành phần được sử dụng trong tất cả các loại pizza được giao là bao nhiêu, sắp xếp theo số lượng từ cao đến thấp?

--tính tổng toppings có sẵn trong pizza ở mỗi đơn hàng được giao
SELECT 
  temp.topping_name
  ,COALESCE(total_toppings, 0) + COALESCE(total_extras, 0) - COALESCE(total_exclusions, 0) final_total 
FROM (
--tổng số lần thành phần trong mỗi đơn hàng theo công thức
SELECT 
  t.topping_name 
  ,COUNT(*) total_toppings
FROM customer_orders c
LEFT JOIN pizza_recipes p ON c.pizza_id = p.pizza_id 
LEFT JOIN pizza_toppings t ON p.topping_id::integer = t.topping_id
GROUP BY 1
ORDER BY 1) temp

LEFT JOIN (
--tổng số lần thành phần được thêm vào ở mỗi lần giao
SELECT 
  topping_name
  ,COUNT(*) total_extras
FROM (SELECT
     	UNNEST(STRING_TO_ARRAY(extras, ', ')::int[]) AS extra
FROM
        customer_orders) c
LEFT JOIN pizza_toppings t ON c.extra = t.topping_id
GROUP BY 1) temp_2 
ON temp.topping_name = temp_2.topping_name

LEFT JOIN (
--tổng số lần topping được loại ra khỏi đơn hàng
SELECT 
  topping_name
  ,COUNT(*) total_exclusions
FROM (SELECT
     	UNNEST(STRING_TO_ARRAY(exclusions, ', ')::int[]) AS exclusion
FROM
        customer_orders) c
LEFT JOIN pizza_toppings t ON c.exclusion = t.topping_id
GROUP BY 1) temp_3

ON temp.topping_name = temp_3.topping_name
ORDER BY 2 DESC

Pricing and Ratings

17.Nếu 1 pizza Meat Lovers có giá $12, Vegetarian có giá $10, và không thêm phí cho sự thay đổi

--thì tổng số tiền Pizza Runner thu được là bao nhiêu (không tính phí giao hàng)?
SELECT 
    SUM(
        CASE 
            WHEN c.pizza_id = 1 THEN 12 
            ELSE 10
        END
    ) AS total_price
FROM customer_orders c
--không tính đơn bị hủy
LEFT JOIN runner_orders r ON c.order_id = r.order_id 
WHERE r.cancellation IS NULL

18.Nếu thêm $1 cho mỗi extras thêm vào (ví dụ thêm cheese thì thêm $1) thì tổng số tiền Pizza Runner thu được là bao nhiêu?

SELECT 
    SUM(total_extra_price) + SUM(total_price) AS grand_total
FROM (
    SELECT 
        COUNT(*) * 1 AS total_extra_price
        ,0 AS total_price
    FROM (
        SELECT
            UNNEST(STRING_TO_ARRAY(extras, ', ')::int[]) AS extra
        FROM
            customer_orders c
        --không tính đơn bị hủy
		LEFT JOIN runner_orders r ON c.order_id = r.order_id 
		WHERE r.cancellation IS NULL
    ) temp
    UNION ALL
    SELECT 
        0 AS total_extra_price
        ,SUM(
            CASE 
                WHEN c.pizza_id = 1 THEN 12 
                ELSE 10
            END
        ) AS total_price
    FROM 
        customer_orders c
	--không tính đơn bị hủy
	LEFT JOIN runner_orders r ON c.order_id = r.order_id 
	WHERE r.cancellation IS NULL
) sub;

19.Nếu 1 pizza Meat Lovers có giá $12, Vegetarian có giá $10 và không thêm phí cho phần extra, mỗi runner được trả $0.30 trên km đi lại - Tổng số tiền Pizza Runner thu được sau khi trừ khoản phí giao hàng cho các runner là bao nhiêu?

SELECT 
    SUM(total_price) - SUM(fee_price) AS total
FROM (
    SELECT 
      SUM(r.distance::numeric)*0.3 fee_price
      ,0 total_price
    FROM runner_orders r
    WHERE r.cancellation IS NULL
    
    UNION ALL
    
    SELECT 
        0 fee_price
        ,SUM(
            CASE 
                WHEN c.pizza_id = 1 THEN 12 
                ELSE 10
            END
        ) AS total_price
    FROM 
        customer_orders c
	--không tính đơn bị hủy
	LEFT JOIN runner_orders r ON c.order_id = r.order_id 
	WHERE r.cancellation IS NULL
) sub;

Conclusion:

Qua case study này, mình có thể luyện tập được cả kỹ năng viết SQL lẫn kỹ năng suy nghĩ, đặt vấn đề khi phân tích dữ liệu sao cho có ý nghĩa và giúp ích cho doanh nghiệp.

Case Study SQL - Pizza Runner

Đây là một thử thách trong chuỗi 8 Week SQL Challenge. Làm sạch, phân tích dữ liệu về một cửa hàng kinh doanh pizza.

Giới thiệu:

ERD:

Data Cleaning:

Data Cleaning:

Pizza Metrics:

Runner and Customer Experience

Ingredient Optimisation

Pricing and Ratings

Conclusion: