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
Execution error
ValueError: Not supported query type!
ALTER TABLE customer_orders
ADD record_id INT IDENTITY(1,1)
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;
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;
SELECT count(order_id) as total_pizza_ordered
FROM customer_orders;
total_pizza_orderedint64
0
14
SELECT COUNT(DISTINCT order_id) as unique_orders
FROM customer_orders
unique_ordersint64
0
10
SELECT runner_id,
COUNT(runner_id) AS successful_orders
FROM runner_orders
WHERE cancellation IS NULL
GROUP BY runner_id
runner_idint64
successful_ordersint64
0
1
4
1
2
3
2
3
1
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;
pizza_idint64
amount_of_dilivered_pizzaint64
0
1
9
1
2
3
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;
customer_idint64
pizza_nameobject
0
101
Meat Lovers
1
101
Vegetarian
2
102
Meat Lovers
3
102
Vegetarian
4
103
Meat Lovers
5
103
Vegetarian
6
104
Meat Lovers
7
105
Vegetarian
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;
order_idint64
number_orderint64
0
4
3
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
customer_idint64
STATUSobject
0
101
NOT CHANGE
1
102
NOT CHANGE
2
103
CHANGE
3
104
CHANGE
4
104
NOT CHANGE
5
105
CHANGE
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
both_exclusions_extrasint64
0
1
SELECT DATEPART(HOUR, order_time) as hour_of_day,
COUNT (order_id) as pizza_count
FROM customer_orders
GROUP BY DATEPART(HOUR, order_time)
SELECT DATENAME(WEEKDAY,[order_time]) as weekday,
COUNT (order_id) as pizza_count
FROM customer_orders
GROUP BY DATENAME(WEEKDAY,[order_time]);
SET DATEFIRST 1;
SELECT DATEPART(WEEK,[registration_date])as week,
COUNT(runner_id) as runner_count
FROM runners
GROUP BY DATEPART(WEEK,[registration_date]);
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;
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
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
customer_idint64
Average_distancefloat64
0
101
20.0
1
102
16.733333333333334
2
103
23.399999999999995
3
104
10.0
4
105
25.0
SELECT max(duration) as longest,
min(duration) as shortest,
max(duration) - min(duration) as dif_longest_shortest
FROM runner_orders
WHERE cancellation is NULL
longestfloat64
shortestfloat64
0
40.0
10.0
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;
runner_idint64
order_idint64
0
1
1
1
1
2
2
1
3
3
1
10
4
2
4
5
2
7
6
2
8
7
3
5
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
runner_idint64
success_percfloat64
0
1
100.0
1
2
75.0
2
3
50.0
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;
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;
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;
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;
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;
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;
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;
total_revenueint64
0
120
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;
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
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;
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