Case Study #2 - Pizza Runner

SELECT * FROM 'runners.csv'

runner_idint64

registration_dateobject

0

1

2021-01-01

1

2

2021-01-03

2

3

2021-01-08

3

4

2021-01-15

SELECT * FROM 'customer-orders.csv'

order_idint64

1 - 10

customer_idint64

101 - 105

0

1

101

1

2

101

2

3

102

3

3

102

4

4

103

5

4

103

6

4

103

7

5

104

8

6

101

9

7

105

10

8

102

11

9

103

12

10

104

13

10

104

SELECT * FROM 'runner-orders.csv'

order_idint64

1 - 10

runner_idint64

1 - 3

0

1

1

1

2

1

2

3

1

3

4

2

4

5

3

5

6

3

6

7

2

7

8

2

8

9

2

9

10

1

SELECT * FROM 'pizza-names.csv'

pizza_idint64

pizza_nameobject

0

1

Meat Lovers

1

2

Vegetarian

SELECT * FROM 'pizza-recipes.csv'

pizza_idint64

toppingsobject

0

1

1-2-3-4-5-6-8-10

1

2

4-6-7-9-11-12

SELECT * FROM 'pizza-toppings.csv'

topping_idint64

1 - 12

topping_nameobject

Bacon8.3%

BBQ Sauce8.3%

10 others83.3%

0

1

Bacon

1

2

BBQ Sauce

2

3

Beef

3

4

Cheese

4

5

Chicken

5

6

Mushrooms

6

7

Onions

7

8

Pepperoni

8

9

Peppers

9

10

Salami

10

11

Tomatoes

11

12

Tomato Sauce

SELECT order_id, customer_id, pizza_id, CASE WHEN exclusions = '' or exclusions like 'null' or exclusions like 'NaN' THEN NULL ELSE exclusions END AS exclusions, CASE WHEN extras = '' OR extras like 'null' or extras like 'NaN' THEN NULL ELSE extras END AS extras, order_time FROM customer_orders;

order_idint64

1 - 10

customer_idint64

101 - 105

0

1

101

1

2

101

2

3

102

3

3

102

4

4

103

5

4

103

6

4

103

7

5

104

8

6

101

9

7

105

10

8

102

11

9

103

12

10

104

13

10

104

SELECT order_id, runner_id, CASE WHEN pickup_time LIKE 'null' THEN NULL ELSE pickup_time END AS pickup_time, CASE WHEN distance LIKE 'null' THEN NULL WHEN distance LIKE '%km' THEN TRIM(distance,'km') ELSE distance END AS distance, CASE WHEN duration LIKE 'null' THEN NULL WHEN duration LIKE '%mins' THEN TRIM(duration,'mins') WHEN duration LIKE '%minute' THEN TRIM(duration,'minute') WHEN duration LIKE '%minutes' THEN TRIM(duration,'minutes') ELSE duration END AS duration, CASE WHEN cancellation LIKE 'null' or cancellation LIKE 'NaN' THEN NULL WHEN cancellation = '' THEN NULL ELSE cancellation END AS cancellation FROM runner_orders;

order_idint64

1 - 10

runner_idint64

1 - 3

0

1

1

1

2

1

2

3

1

3

4

2

4

5

3

5

6

3

6

7

2

7

8

2

8

9

2

9

10

1

ALTER TABLE runner_orders ALTER COLUMN pickup_time DATETIME ALTER TABLE runner_orders ALTER COLUMN distance FLOAT ALTER TABLE runner_orders ALTER COLUMN duration INT; ALTER TABLE pizza_names ALTER COLUMN pizza_name VARCHAR(MAX); ALTER TABLE pizza_recipes ALTER COLUMN toppings VARCHAR(MAX); ALTER TABLE pizza_toppings ALTER COLUMN topping_name VARCHAR(MAX)

import pandas as pd import datetime

runner_orders["distance"] = pd.to_numeric(runner_orders["distance"]) runner_orders["duration"] = pd.to_numeric(runner_orders["duration"])

DROP TABLE IF EXISTS pizza_recipes; SELECT pizza_id, TRIM(topping_id.value) as topping_id, topping_name INTO pizza_recipes FROM pizza_recipes as p CROSS APPLY string_split(p.toppings, ',') as topping_id INNER JOIN pizza_toppings p2 ON TRIM(topping_id.value) = p2.topping_id

There’s an error in this block

Try running the app again, or contact the app’s creator

ALTER TABLE customer_orders ADD record_id INT IDENTITY(1,1)

There has been an error in the blocks above

Run the app again or contact app’s creator

DROP TABLE IF EXISTS extras SELECT c.record_id, TRIM(e.value) AS topping_id INTO extras FROM customer_orders as c CROSS APPLY string_split(c.extras, ',') as e;

There has been an error in the blocks above

Run the app again or contact app’s creator

DROP TABLE IF EXISTS exclusions SELECT c.record_id, TRIM(e.value) AS topping_id INTO exclusions FROM customer_orders as c CROSS APPLY string_split(c.exclusions, ',') as e;

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT count(order_id) as total_pizza_ordered FROM customer_orders;

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT COUNT(DISTINCT order_id) as unique_orders FROM customer_orders

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT runner_id, COUNT(runner_id) AS successful_orders FROM runner_orders WHERE cancellation IS NULL GROUP BY runner_id

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT pizza_id, COUNT(pizza_id) as amount_of_dilivered_pizza FROM customer_orders as c RIGHT JOIN runner_orders as r ON c.order_id = r.order_id WHERE r.cancellation IS null GROUP BY pizza_id;

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT customer_id, p.pizza_name, COUNT(c.pizza_id) as amount_pizza FROM customer_orders as c INNER JOIN pizza_names as p ON c.pizza_id =p.pizza_id GROUP BY customer_id,pizza_name ORDER BY customer_id;

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT c.order_id, COUNT(c.order_id) as number_order FROM customer_orders as c RIGHT JOIN runner_orders as r ON c.order_id = r.order_id WHERE r.cancellation is NULL GROUP BY c.order_id ORDER BY number_order DESC LIMIT 1;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH Status_table AS ( SELECT order_id, customer_id, pizza_id, exclusions, extras, CASE WHEN exclusions is not null or extras is not null THEN 'CHANGE' ELSE 'NOT CHANGE' END AS STATUS FROM customer_orders ) SELECT customer_id, STATUS, COUNT(STATUS) as count FROM Status_table as s RIGHT JOIN runner_orders as r ON s.order_id = r.order_id WHERE r.cancellation is NULL GROUP BY customer_id,STATUS ORDER BY customer_id

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT count(c.order_id) as both_exclusions_extras FROM customer_orders as c RIGHT JOIN runner_orders as r ON c.order_id = r.order_id WHERE exclusions is not null and extras is not null and r.cancellation is null

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT DATEPART(HOUR, order_time) as hour_of_day, COUNT (order_id) as pizza_count FROM customer_orders GROUP BY DATEPART(HOUR, order_time)

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT DATENAME(WEEKDAY,[order_time]) as weekday, COUNT (order_id) as pizza_count FROM customer_orders GROUP BY DATENAME(WEEKDAY,[order_time]);

There has been an error in the blocks above

Run the app again or contact app’s creator

SET DATEFIRST 1; SELECT DATEPART(WEEK,[registration_date])as week, COUNT(runner_id) as runner_count FROM runners GROUP BY DATEPART(WEEK,[registration_date]);

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH time_table AS (SELECT DISTINCT runner_id, r.order_id, order_time, pickup_time, CAST(DATEDIFF(minute,order_time,pickup_time) AS FLOAT) as time FROM customer_orders as c INNER JOIN runner_orders as r ON C.order_id = r.order_id WHERE r.cancellation IS NULL GROUP BY runner_id,r.order_id,order_time, pickup_time ) SELECT runner_id, AVG(time) AS average_time FROM time_table GROUP BY runner_id;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH CTE AS (SELECT c.order_id, COUNT(c.order_id) as pizza_order, order_time, pickup_time, CAST(DATEDIFF( minute,order_time,pickup_time) AS FLOAT) as time FROM customer_orders as c INNER JOIN runner_orders as r ON c.order_id = r.order_id WHERE r.cancellation IS NULL GROUP BY c.order_id,order_time, pickup_time ) SELECT pizza_order, AVG(time) AS avg_time_per_order, (AVG(time)/ pizza_order) AS avg_time_per_pizza FROM CTE GROUP BY pizza_order

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT customer_id, AVG(distance) AS Average_distance FROM customer_orders as c INNER JOIN runner_orders as r ON c.order_id = r.order_id WHERE r.cancellation is NULL GROUP BY customer_id

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT max(duration) as longest, min(duration) as shortest, max(duration) - min(duration) as dif_longest_shortest FROM runner_orders WHERE cancellation is NULL

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT runner_id, order_id, ROUND(AVG(distance/duration*60),2) as avg_time FROM runner_orders WHERE cancellation is NULL GROUP BY runner_id,order_id ORDER BY runner_id;

There has been an error in the blocks above

Run the app again or contact app’s creator

with CTE AS ( SELECT runner_id, order_id, CASE WHEN cancellation is NULL THEN 1 ELSE 0 END AS Sucess_delivery FROM runner_orders ) SELECT runner_id, round( 100*sum(sucess_delivery)/count(*),0) as success_perc FROM CTE group by runner_id

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH CTE AS ( SELECT pizza_id, topping_name FROM pizza_recipes as p1 INNER JOIN pizza_toppings as p2 ON p1.topping_id = p2.topping_id ) SELECT pizza_id, String_agg(topping_name,',') as Standard_toppings FROM CTE GROUP BY pizza_id;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH CTE AS (SELECT pizza_id, topping_type, topping FROM (SELECT pizza_id, CAST(SUBSTRING(extras, 1,1) AS INT) AS topping_1, CAST(SUBSTRING(extras,3,3) AS INT) as topping_2 FROM customer_orders WHERE extras is not null) p UNPIVOT (topping for topping_type in (topping_1,topping_2)) as unpvt) SELECT Topping, topping_name, COUNT(topping) AS Extra_Topping_Time FROM CTE c JOIN pizza_toppings p ON c.topping = p.topping_id WHERE topping != 0 GROUP BY topping,topping_name;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH CTE AS (SELECT pizza_id, topping_type, topping FROM (SELECT pizza_id, CAST(SUBSTRING(exclusions, 1,1) AS INT) AS exclusions_1, CAST(SUBSTRING(exclusions,3,3) AS INT) as exclusions_2 FROM customer_orders WHERE exclusions is not null) p UNPIVOT (topping for topping_type in (exclusions_1,exclusions_2)) as unpvt) SELECT Topping, topping_name, count(topping) AS exclusions_Topping_Time FROM CTE c JOIN pizza_toppings p ON c.topping = p.topping_id WHERE topping != 0 GROUP BY topping,topping_name ORDER BY exclusions_Topping_Time DESC;

There has been an error in the blocks above

Run the app again or contact app’s creator

with extras_cte AS ( SELECT record_id, 'Extra ' + STRING_AGG(t.topping_name, ', ') as record_options FROM extras e, pizza_toppings t WHERE e.topping_id = t.topping_id GROUP BY record_id ), exclusions_cte AS ( SELECT record_id, 'Exclude ' + STRING_AGG(t.topping_name, ', ') as record_options FROM exclusions e, pizza_toppings t WHERE e.topping_id = t.topping_id GROUP BY record_id ), union_cte AS ( SELECT * FROM extras_cte UNION SELECT * FROM exclusions_cte ) SELECT c.record_id, c.order_id, CONCAT_WS(' - ', p.pizza_name, STRING_AGG(cte.record_options, ' - ')) as pizza_and_topping FROM customer_orders c JOIN pizza_names p ON c.pizza_id = p.pizza_id LEFT JOIN union_cte cte ON c.record_id = cte.record_id GROUP BY c.record_id, p.pizza_name, c.order_id ORDER BY 1;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH INGREDIENT_CTE AS (SELECT record_id, pizza_name, CASE WHEN p1.topping_id in ( SELECT topping_id FROM extras e WHERE C.record_id = e.record_id ) THEN '2x' + p1.topping_name ELSE p1.topping_name END AS topping FROM customer_orders c JOIN pizza_names p2 ON c.pizza_id = p2.pizza_id JOIN pizza_recipes p1 ON c.pizza_id = p1.pizza_id WHERE p1.topping_id NOT IN (SELECT topping_id FROM exclusions e WHERE e.record_id = c.record_id) ) SELECT record_id, CONCAT(pizza_name +':' ,STRING_AGG(topping, ',' ) WITHIN GROUP (ORDER BY topping ASC)) AS ingredient_list FROM INGREDIENT_CTE GROUP BY record_id,pizza_name ORDER BY 1;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH INGREDIENT_CTE AS (SELECT record_id, pizza_name, topping_name, CASE WHEN p1.topping_id in ( SELECT topping_id FROM extras e WHERE C.record_id = e.record_id ) THEN 2 ELSE 1 END AS times_used_topping FROM customer_orders c JOIN pizza_names p2 ON c.pizza_id = p2.pizza_id JOIN pizza_recipes p1 ON c.pizza_id = p1.pizza_id JOIN runner_orders r ON c.order_id = r.order_id WHERE p1.topping_id NOT IN (SELECT topping_id FROM exclusions e WHERE e.record_id = c.record_id) and r.cancellation is NULL ) SELECT topping_name, SUM(times_used_topping) AS times_used_topping from INGREDIENT_CTE GROUP BY topping_name order by times_used_topping desc; SELECT topping_name, SUM(times_used_topping) AS times_used_topping from INGREDIENT_CTE GROUP BY topping_name order by times_used_topping desc;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH CTE AS (SELECT pizza_id, pizza_name, CASE WHEN pizza_name = 'Meatlovers' THEN 12 ELSE 10 END AS pizza_cost FROM pizza_names) SELECT SUM(pizza_cost) as total_revenue FROM customer_orders c JOIN runner_orders r ON c.order_id = r.order_id JOIN CTE c2 ON c.pizza_id = c2.pizza_id WHERE r.cancellation is NULL;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH pizza_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 r JOIN customer_orders c ON c.order_id = r.order_id WHERE r.cancellation IS NULL ) SELECT SUM(CASE WHEN extras IS NULL THEN pizza_cost WHEN DATALENGTH(extras) = 1 THEN pizza_cost + 1 ELSE pizza_cost + 2 END ) AS total_earn FROM pizza_cte;

There has been an error in the blocks above

Run the app again or contact app’s creator

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

There has been an error in the blocks above

Run the app again or contact app’s creator

SELECT customer_id , c.order_id, runner_id, rating, order_time, pickup_time, datepart( minute,pickup_time - order_time) as Time__order_pickup, r.duration, round(avg(distance/duration*60),2) as avg_Speed, COUNT(pizza_id) AS Pizza_Count FROM customer_orders c LEFT JOIN runner_orders r ON c.order_id = r.order_id LEFT JOIN ratings r2 ON c.order_id = r2.order_id WHERE r.cancellation is NULL GROUP BY customer_id , c.order_id, runner_id, rating, order_time, pickup_time, datepart( minute,pickup_time - order_time) , r.duration ORDER BY c.customer_id;

There has been an error in the blocks above

Run the app again or contact app’s creator

WITH CTE AS (SELECT c.order_id, SUM(CASE WHEN pizza_name = 'Meatlovers' THEN 12 ELSE 10 END) AS pizza_cost FROM pizza_names p JOIN customer_orders c ON p.pizza_id =c.pizza_id GROUP BY c.order_id) SELECT SUM(pizza_cost) AS revenue, SUM(distance) *0.3 as total_cost, SUM(pizza_cost) - SUM(distance)*0.3 as profit FROM runner_orders r JOIN CTE c ON R.order_id =C.order_id WHERE r.cancellation is NULL

There has been an error in the blocks above

Run the app again or contact app’s creator