Bevor Sie mit der Lösung der Fragen beginnen, stellen Sie fest, dass die angegebenen Daten nicht korrekt oder schlampig sind. Daher müssen wir zunächst einige Datenbereinigungsverfahren durchführen, um sie zu entfernen.
Zuerst reinigen wir das customer_orders Tisch. Wie wir sehen, gibt es viele leere Zellen und einige falsch eingegebene NULL-Werte. Additionally korrigieren wir das, indem wir „null“ und leere Zellen durch NULL-Werte ersetzen.
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);
Dann putzen wir runner_orders. Es gibt falsch eingegebene Nullwerte, leere Zellen usw distance Und periodwerden in die gleiche Einheit standardisiert und die Einheit wird aus den Daten entfernt, um die Berechnungen durchführbar zu machen.
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),
period =
(CASE
WHEN period LIKE '%null%' THEN NULL
WHEN period LIKE '%minutes%' THEN REPLACE(period, 'minutes', '')
WHEN period LIKE '%minutes%' THEN REPLACE(period, 'minutes', '')
WHEN period LIKE '%minute%' THEN REPLACE(period, 'minute', '')
ELSE period
END),
cancellation =
(CASE
WHEN cancellation LIKE '%null' OR cancellation = '' THEN NULL
ELSE cancellation
END);
Lösungen:
(A) Pizza-Kennzahlen
[1] Wie viele Pizzen wurden bestellt?
SELECT COUNT(*) AS Pizzas_Ordered FROM customer_orders_2;
[2] Wie viele einzelne Kundenbestellungen wurden getätigt?
SELECT COUNT(DISTINCT order_id) AS Orders_Made FROM customer_orders_2;
[3] Wie viele erfolgreiche Bestellungen wurden von jedem Läufer ausgeliefert?
SELECT runner_id,COUNT(order_id) FROM runner_orders_2
WHERE cancellation IS NULL
GROUP BY runner_id;
[4] Wie viele Pizzen jeder Sorte wurden geliefert?
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] Wie viele Vegetarier und Fleischliebhaber wurden von jedem Kunden bestellt?
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] Wie viele Pizzen wurden maximal in einer Bestellung geliefert?
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] Bei wie vielen gelieferten Pizzen wurde für jeden Kunden mindestens eine Änderung vorgenommen, bei wie vielen gab es keine Änderungen?
SELECT c.customer_id,
COUNT(CASE WHEN c.exclusions IS NOT NULL OR c.extras IS NOT NULL THEN 1 END) AS Adjustments,
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] Wie viele Pizzen wurden geliefert, die sowohl Ausschlüsse als auch Extras hatten?
SELECT
COUNT(CASE WHEN c.exclusions IS NOT NULL AND c.extras IS NOT NULL THEN 1 END) AS Adjustments
FROM customer_orders_2 c
JOIN runner_orders_2 r
ON c.order_id = r.order_id
WHERE r.cancellation IS NULL;
[9] Wie hoch conflict die Gesamtmenge der professional Stunde des Tages bestellten Pizzen?
SELECT
HOUR(order_time) AS hour,
COUNT(order_id) AS total_volume
FROM customer_orders_2
GROUP BY HOUR(order_time);
[10] Wie hoch conflict das Bestellvolumen für jeden Wochentag?
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) Läufer- und Kundenerlebnis
[1] Wie viele Läufer haben sich für jeden einwöchigen Zeitraum angemeldet? (d. h. zu Beginn der Woche). 2021-01-01)
SELECT
WEEK(registration_date) AS WeekNumber,
COUNT(runner_id) AS NoOfRunners
FROM runners
GROUP BY WeekNumber;
[2] Wie lange dauerte es durchschnittlich in Minuten, bis jeder Läufer im Hauptquartier von Pizza Runner ankam, um die Bestellung abzuholen?
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] Gibt es einen Zusammenhang zwischen der Anzahl der Pizzen und der Zubereitungsdauer der Bestellung?
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] Gibt es einen Zusammenhang zwischen der Anzahl der Pizzen und der Zubereitungsdauer der Bestellung?
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] Was conflict der Unterschied zwischen der längsten und der kürzesten Lieferzeit aller Bestellungen?
SELECT MAX(period) - MIN(period) AS DeliveryTimeRange FROM runner_orders_2;
[6] Wie hoch conflict die durchschnittliche Geschwindigkeit für jeden Läufer bei jeder Lieferung, und können Sie bei diesen Werten einen Development erkennen?
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] Wie hoch ist der Prozentsatz erfolgreicher Lieferungen für jeden Läufer?
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) Zutatenoptimierung
[1] Was sind die Standardzutaten für jede Pizza?
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] Welches Additional wurde am häufigsten hinzugefügt?
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] Was conflict der häufigste Ausschluss?
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) Preise und Bewertungen
[1] Wenn eine Meat Lovers-Pizza 12 US-Greenback und eine Vegetarisch-Pizza 10 US-Greenback kostet und für Änderungen keine Gebühren anfallen – wie viel Geld hat Pizza Runner bisher verdient, wenn keine Liefergebühren anfallen?
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] Was wäre, wenn für Pizza-Extras eine zusätzliche Gebühr von 1 US-Greenback erhoben würde? [Extra cheese is $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] Das Pizza Runner-Crew möchte nun ein zusätzliches Bewertungssystem hinzufügen, mit dem Kunden ihren Läufer bewerten können. Wie würden Sie eine zusätzliche Tabelle für diesen neuen Datensatz entwerfen? Erstellen Sie ein Schema für diese neue Tabelle und fügen Sie Ihre eigenen Daten für Bewertungen für jeden erfolgreichen Kunden ein Reihenfolge zwischen 1 und 5.
DROP TABLE IF EXISTS scores;
CREATE TABLE scores
(order_id INTEGER,
score INTEGER);
INSERT INTO scores
(order_id ,score)
VALUES
(1,3),
(2,4),
(3,5),
(4,2),
(5,1),
(6,3),
(7,4),
(8,1),
(9,3),
(10,5);SELECT * FROM scores;
[4] Können Sie mithilfe Ihrer neu erstellten Tabelle alle Informationen zu einer Tabelle zusammenfügen, die die folgenden Informationen für erfolgreiche Lieferungen enthält? [customer_id, order_id, runner_id, rating, order_time, pickup_time, Time between order & pickup, Delivery Duration, Average Speed, Total Number of Pizzas]
SELECT c.customer_id, c.order_id, r.runner_id, rt.score, c.order_time,
r.pickup_time, TIMESTAMPDIFF(minute, order_time, pickup_time) as delivery_delay,
r.period, ROUND(avg(r.distance*60/r.period),1) as avg_speed,
rely(c.pizza_id) as PizzaCount
FROM customer_orders_2 c
JOIN runner_orders_2 r
ON c.order_id = r.order_id
JOIN scores rt
ON rt.order_id = c.order_id
GROUP BY c.customer_id, c.order_id, r.runner_id, rt.score, c.order_time,
r.pickup_time, delivery_delay, r.period
ORDER BY c.customer_id;
[5] Wenn eine „Meat Lovers“-Pizza 12 $ und eine vegetarische Pizza 10 $ zum Festpreis ohne Extrakosten kostete und jeder Läufer 0,30 $ professional zurückgelegten Kilometer erhält – wie viel Geld bleibt „Pizza Runner“ nach diesen Lieferungen übrig?
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;
