Присоединяйтесь ко мне в моем 8-недельном SQL-приключении

Это вторая неделя моего 8-недельного SQL-приключения от 8weeksqlchallenge.com, где я перейду от нуля к герою в SQL.

Прежде чем углубиться в эту статью, если вы пропустили решения задач первой недели, нажмите здесь.

Ставьте Нравится, делитесь и подписывайтесь на меня на Medium, чтобы получать больше интересных статей на темы Data Analytics, Data Viz, AI & ML и т. д.

В этой статье я поделюсь с вами своим подходом ко второй задаче: решению примера Pizza Runner, вымышленного ресторана, который предлагает и доставляет пиццу и хочет уберизировать ее. Если вы заинтересованы присоединиться ко мне в этом путешествии или хотите проверить свои навыки SQL, вы можете прочитать оригинальный пример из практики здесь.

Введение:

Знаете ли вы, что ежедневно во всем мире потребляется более 115 миллионов килограммов пиццы??? (Ну, согласно Википедии, во всяком случае…)

Дэнни просматривал свою ленту в Instagram, когда что-то действительно привлекло его внимание — «Ретро-стиль 80-х и пицца — это будущее!»

Дэнни увлекся этой идеей, но он знал, что пицца сама по себе не поможет ему получить начальное финансирование для расширения своей новой Империи пиццы, поэтому у него была еще одна гениальная идея, которую он мог объединить с ней — он собирался уберизовать это — и вот Pizza Runner был запущен!

Дэнни начал с найма «бегунов» для доставки свежей пиццы из штаб-квартиры Pizza Runner (также известной как дом Дэнни), а также полностью исчерпал свою кредитную карту, чтобы заплатить внештатным разработчикам за создание мобильного приложения для приема заказов от клиентов.

Доступные данные:

В этой задаче 6 таблиц. Таблицы следующие:

runners
customer_orders
runner_orders
pizza_names
pizza_recipes
pizza_toppings

База данных и таблицы:

Код для создания необходимой базы данных и таблиц приведен здесь.

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

Тематическое исследование разбито на 4 области фокуса,

A – Показатели пиццы

B – Runner & Customer Experience

C — оптимизация ингредиентов

D — Цены и рейтинги

Перед тем, как приступить к решению вопросов, данные приведены неверно или неаккуратно. Поэтому сначала нам нужно выполнить некоторые процедуры очистки данных, чтобы избавиться от них.

Сначала мы очищаем таблицу customer_orders. Как мы видим, есть много пустых ячеек и некоторые неправильно введенные значения NULL. Поэтому мы исправим это, заменив «нулевые» и пустые ячейки значениями NULL.

DROP TABLE IF EXISTS customer_orders_2;
CREATE TABLE customer_orders_2 AS 
SELECT * FROM customer_orders;

UPDATE customer_orders_2
SET exclusions = 
(CASE WHEN exclusions ='' OR exclusions LIKE '%null%'
THEN NULL
ELSE exclusions END),
extras = 
(CASE WHEN extras='' OR extras LIKE '%null%' OR extras IS NULL
THEN NULL
ELSE extras END);

Затем чистим runner_orders. Имеются ошибочно введенные нулевые значения, пустые ячейки, а distance и duration будут стандартизированы в одну и ту же единицу измерения, и эта единица будет удалена из данных для возможности расчетов.

DROP TABLE IF EXISTS runner_orders_2;
CREATE TABLE runner_orders_2 AS
SELECT * FROM runner_orders;

UPDATE runner_orders_2
SET pickup_time = 
    (CASE 
        WHEN pickup_time LIKE '%null%' THEN NULL
        ELSE pickup_time 
     END),
    distance = 
    (CASE
        WHEN distance LIKE '%null%' THEN NULL
        WHEN distance LIKE '%km' THEN REPLACE(distance, 'km', '')
        ELSE distance
     END),
    duration = 
    (CASE 
        WHEN duration LIKE '%null%' THEN NULL
        WHEN duration LIKE '%minutes%' THEN REPLACE(duration, 'minutes', '')
        WHEN duration LIKE '%mins%' THEN REPLACE(duration, 'mins', '')
        WHEN duration LIKE '%minute%' THEN REPLACE(duration, 'minute', '')
        ELSE duration
     END),
    cancellation =
    (CASE 
        WHEN cancellation LIKE '%null' OR cancellation = '' THEN NULL
        ELSE cancellation
     END);

Решения:

(A) Показатели пиццы

[1]Сколько пицц было заказано?

SELECT COUNT(*) AS Pizzas_Ordered FROM customer_orders_2;

[2]Сколько уникальных заказов клиентов было сделано?

SELECT COUNT(DISTINCT customer_id) AS Orders_Made FROM customer_orders_2;

[3]Сколько успешных заказов было доставлено каждым бегуном?

SELECT runner_id,COUNT(order_id) FROM runner_orders_2
WHERE cancellation IS NULL
GROUP BY runner_id;

[4]Сколько пицц каждого типа было доставлено?

SELECT customer_orders_2.pizza_id,pizza_names.pizza_name,COUNT(customer_orders_2.pizza_id) AS PizzaCount 
FROM customer_orders_2
JOIN runner_orders_2
ON customer_orders_2.order_id = runner_orders_2.order_id
JOIN pizza_names
ON customer_orders_2.pizza_id = pizza_names.pizza_id
WHERE runner_orders_2.cancellation IS NULL
GROUP BY pizza_id;

[5]Сколько вегетарианцев и любителей мяса заказал каждый покупатель?

SELECT c.customer_id,
COUNT(CASE WHEN p.pizza_name = 'Vegetarian' then 1 END) AS Vegetarians,
COUNT(CASE WHEN p.pizza_name = 'Meatlovers' then 1 END) AS Meatlovers
FROM customer_orders_2 c
JOIN pizza_names p
ON c.pizza_id = p.pizza_id
GROUP BY c.customer_id;

[6]Какое максимальное количество пицц было доставлено в одном заказе?

SELECT c.order_id,COUNT(c.pizza_id) AS No_of_Pizzas
FROM customer_orders_2 c
JOIN runner_orders_2 r
ON c.order_id = r.order_id
WHERE r.cancellation IS NULL
GROUP BY c.order_id
ORDER BY No_of_Pizzas DESC
LIMIT 1;

[7]Для каждого клиента, сколько доставленных пицц было изменено хотя бы 1 раз, а сколько не было изменено?

SELECT c.customer_id,
COUNT(CASE WHEN c.exclusions IS NOT NULL OR c.extras IS NOT NULL THEN 1 END) AS Changes,
COUNT(CASE WHEN c.exclusions IS NULL AND c.extras IS NULL THEN 1 END) AS NoChanges
FROM customer_orders_2 c
JOIN runner_orders_2 r
ON c.order_id = r.order_id
WHERE r.cancellation IS NULL
GROUP BY c.customer_id;

[8]Сколько пицц было доставлено с исключениями и дополнениями?

SELECT
COUNT(CASE WHEN c.exclusions IS NOT NULL AND c.extras IS NOT NULL THEN 1 END) AS Changes
FROM customer_orders_2 c
JOIN runner_orders_2 r
ON c.order_id = r.order_id
WHERE r.cancellation IS NULL;

[9]Каков был общий объем пиццы, заказанной за каждый час дня?

SELECT
    HOUR(order_time) AS hour,
    COUNT(order_id) AS total_volume
FROM customer_orders_2
GROUP BY HOUR(order_time);

[10]Каков был объем заказов в каждый день недели?

SELECT
    DAYNAME(order_time) AS day_of_week,
    COUNT(order_id) AS total_volume
FROM customer_orders_2
GROUP BY DAYNAME(order_time)
ORDER BY total_volume DESC;

(B) Runner и клиентский опыт

[1]Сколько бегунов зарегистрировалось на каждую неделю? (т. е. начало недели 2021-01-01)

SELECT 
WEEK(registration_date) AS WeekNumber,
COUNT(runner_id) AS NoOfRunners 
FROM runners
GROUP BY WeekNumber;

[2]Сколько в среднем времени в минутах потребовалось каждому курьеру, чтобы добраться до штаб-квартиры Pizza Runner, чтобы забрать заказ?

SELECT r.runner_id,
ROUND(AVG(TIMESTAMPDIFF(MINUTE,c.order_time,r.pickup_time)),1) AS Avg_Pickup_Time
FROM customer_orders_2 c
JOIN runner_orders_2 r
ON c.order_id = r.order_id
GROUP BY r.runner_id;

[3]Есть ли какая-либо связь между количеством пицц и тем, сколько времени требуется для приготовления заказа?

WITH pizza_prep AS(
SELECT c.order_id,COUNT(c.order_id) AS NoOfPizza,
ROUND(AVG(TIMESTAMPDIFF(MINUTE,c.order_time,r.pickup_time)),1) AS prep_time
FROM customer_orders_2 c
JOIN runner_orders_2 r USING(order_id)
WHERE r.cancellation IS NULL
GROUP BY c.order_id)

SELECT NoOfPizza,prep_time FROM pizza_prep GROUP BY NoOfPizza;

[4]Есть ли какая-либо связь между количеством пицц и тем, сколько времени требуется для приготовления заказа?

SELECT c.customer_id, ROUND(AVG(r.distance),2) AS AvgDistance
FROM customer_orders_2 c
JOIN runner_orders_2 r USING(order_id)
WHERE r.cancellation IS NULL AND r.distance IS NOT NULL
GROUP BY c.customer_id;

[5]Какова разница между самым длинным и самым коротким временем доставки для всех заказов?

SELECT MAX(duration) - MIN(duration) AS DeliveryTimeRange FROM runner_orders_2;

[6]Какова была средняя скорость каждого бегуна при каждой доставке и замечаете ли вы какие-либо тенденции в этих значениях?

SELECT r.runner_id,
ROUND((r.distance/(TIMESTAMPDIFF(MINUTE,c.order_time,r.pickup_time)))*60,2) AS Speed_kmph
FROM customer_orders_2 c
JOIN runner_orders_2 r USING(order_id)
WHERE r.cancellation IS NULL AND r.pickup_time IS NOT NULL
GROUP BY r.runner_id;

[7]Каков процент успешных показов для каждого бегуна?

SELECT runner_id,
ROUND(((SUM(CASE WHEN cancellation IS NULL then 1 ELSE 0 END)/COUNT(order_id))*100),2) AS Success_Percent
FROM runner_orders_2
GROUP BY runner_id;

(C) Оптимизация ингредиентов

[1]Каковы стандартные ингредиенты для каждой пиццы?

DROP TABLE if EXISTS pizza_recipes_mod;
CREATE TABLE pizza_recipes_mod(
pizza_id INTEGER,
topping_id INTEGER);

INSERT INTO pizza_recipes_mod VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,8),
(1,10),
(2,4),
(2,6),
(2,7),
(2,9),
(2,11),
(2,12);

SELECT p.pizza_id,n.pizza_name,GROUP_CONCAT(t.topping_name) AS Toppings
FROM pizza_recipes_mod p
JOIN pizza_toppings t USING(topping_id)
JOIN pizza_names n USING(pizza_id)
GROUP BY p.pizza_id;

[2]Что чаще всего добавляли?

WITH cte AS
     (SELECT substring_index(extras,',', 1) AS extras1 
      FROM customer_orders
      UNION ALL
      SELECT substring_index(extras,',', -1)
      FROM customer_orders)
SELECT COUNT(topping_name), topping_name FROM cte JOIN pizza_toppings p ON p.topping_id = cte.extras1
GROUP BY topping_name
ORDER BY COUNT(topping_name) DESC
LIMIT 1;

[3]Что чаще всего исключали?

WITH cte AS
(SELECT substring_index(exclusions,',', 1) AS exclusions
  FROM customer_orders
  UNION ALL
  SELECT substring_index(exclusions,',', -1)
  FROM customer_orders) 
SELECT COUNT(topping_name), topping_name FROM cte JOIN pizza_toppings p ON p.topping_id = cte.exclusions
GROUP BY topping_name
ORDER BY COUNT(topping_name) desc
LIMIT 1;

(D) Цены и рейтинги

[1]Если пицца Meat Lovers стоит 12 долларов США, а вегетарианская – 10 долларов США, и плата за изменения не взималась, сколько денег заработала Pizza Runner, если не взимать плату за доставку?

SELECT SUM(CASE WHEN pizza_id = 1 then 12
when pizza_id = 2 then 10 END) AS Total_Earnings
FROM runner_orders_2
JOIN customer_orders_2 USING(order_id)
WHERE cancellation IS NULL;

[2]Что, если за пиццу взимается дополнительная плата в размере 1 доллара США? [Дополнительный сыр стоит 1 доллар]

WITH cte AS
(SELECT 
 (CASE WHEN pizza_id=1 THEN 12
    WHEN pizza_id = 2 THEN 10
    END) AS pizza_cost, 
    c.exclusions,
    c.extras
 FROM runner_orders_2 r
JOIN customer_orders_2 c ON c.order_id = r.order_id
WHERE r.cancellation IS  NULL)
SELECT 
 SUM(CASE WHEN extras IS NULL THEN pizza_cost
  WHEN LENGTH(extras) = 1 THEN pizza_cost + 1
        ELSE pizza_cost + 2
        END) AS Cost_with_Extras
FROM cte;

[3]Команда Pizza Runner теперь хочет добавить дополнительную систему оценок, которая позволит клиентам оценивать своих бегунов. Как бы вы разработали дополнительную таблицу для этого нового набора данных — создайте схему для этой новой таблицы и вставьте свои собственные данные для оценок для каждого успешного заказа клиента от 1 до 5.

DROP TABLE IF EXISTS ratings;
CREATE TABLE ratings 
 (order_id INTEGER,
    rating INTEGER);
INSERT INTO ratings
 (order_id ,rating)
VALUES 
(1,3),
(2,4),
(3,5),
(4,2),
(5,1),
(6,3),
(7,4),
(8,1),
(9,3),
(10,5);

SELECT * FROM ratings;

[4]Используя только что созданную таблицу, можете ли вы объединить всю информацию вместе, чтобы сформировать таблицу, которая содержит следующую информацию для успешной доставки? [customer_id, order_id, runner_id, rating, order_time, pickup_time, время между заказом и получением, продолжительность доставки, средняя скорость, общее количество пицц]

SELECT c.customer_id, c.order_id, r.runner_id, rt.rating, c.order_time,
 r.pickup_time, TIMESTAMPDIFF(minute, order_time, pickup_time) as delivery_delay, 
    r.duration, ROUND(avg(r.distance*60/r.duration),1) as avg_speed, 
    count(c.pizza_id) as PizzaCount
FROM customer_orders_2 c
JOIN runner_orders_2 r
ON c.order_id = r.order_id
JOIN ratings rt
ON rt.order_id = c.order_id
GROUP BY c.customer_id, c.order_id, r.runner_id, rt.rating, c.order_time,
r.pickup_time, delivery_delay, r.duration
ORDER BY c.customer_id;

[5]Если пицца Meat Lovers стоила 12 долларов США, а вегетарианская – 10 долларов США по фиксированной цене без дополнительных расходов, а каждому бегуну платили 0,30 доллара США за пройденный километр, сколько денег осталось у Pizza Runner после этих доставок?

SELECT 
 ROUND(SUM(CASE WHEN pizza_id=1 THEN 12
    WHEN pizza_id = 2 THEN 10
    END)  - SUM((r.distance+0) * 0.3),2) AS pizza_cost,
    (SUM(r.distance+0) * 0.3) AS distance_cost,
    SUM(CASE WHEN pizza_id=1 THEN 12
    WHEN pizza_id = 2 THEN 10
    END ) AS Total_Cost
FROM runner_orders_2 r
JOIN customer_orders_2 c ON c.order_id = r.order_id
WHERE r.cancellation IS  NULL;

Заключение:

В заключение, эта статья обрисовала в общих чертах вторую неделю 8-недельного приключения SQL. Я узнал об объединениях, агрегировании, общих табличных выражениях (CTE), функциях времени и т. д., решая вопросы из тематического исследования на этой неделе.

Я также хотел бы поблагодарить вас за то, что вы прочитали этот вывод. Я надеюсь, что вы нашли его полезным и информативным. Если у вас есть какие-либо вопросы или комментарии, пожалуйста, не стесняйтесь оставлять их ниже.

Решения для Недели 3 этой задачи будут опубликованы в следующие выходные.