365 Days of Daily Coding: Day 101

Hello readers! I hope you had a good day. We are having lock down here in Kuala Lumpur. We are only allowed to travel a distance of 10km from our home for necessities and emergencies. I also hope that you all have either been vaccinated or waiting to be vaccinated. It’s of utmost importance to be vaccinated to reduce the severity of your symptoms if you test positive as well as to save the most vulnerable groups from our community. It’s the same reason why we wear masks, isn’t it?

I have already registered to be vaccinated. I am waiting for my appointment to be fixed. I have heard that after vaccination there’s weakness/tiredness as well as soreness around the vaccinated area. I need to be mentally prepared for that too.

I realised that I did not complete the bonus questions of Week 1 which I did today and also completed the questions under the “Pizza Metrics” from Week 2. There are quite a lot of questions for Week 2.

Something new that I learned today is the difference between ‘NULL’ and blank values as I was required to clean two tables before quering the tables.

NULL means that it is an unknown value or absence of value where as blank values means that it is a value but is just empty. Hence, NULL have no memories allocated where as blank values have memories allocated. NULL can be assigned to columns of any data types where as blank values can only be assigned to columns of string data type. You can learn more from here.

--Pizza Metrics
--How many pizzas were ordered?
SELECT COUNT(*) FROM customer_orders co;

--How many unique customer orders were made?
SELECT COUNT(DISTINCT order_id) FROM customer_orders co;

--How many successful orders were delivered by each runner?
SELECT COUNT(*) FROM runner_orders ro
WHERE cancellation = "" OR cancellation IS NULL;

--How many of each type of pizza was delivered?
SELECT co.pizza_id, COUNT(*)
FROM customer_orders co JOIN runner_orders ro
ON co.order_id = ro.order_id
WHERE ro.cancellation = ''
GROUP BY co.pizza_id;

--How many Vegetarian and Meatlovers were ordered by each customer?
SELECT pn.pizza_name, COUNT(*)
FROM customer_orders co JOIN pizza_names pn 
ON co.pizza_id = pn.pizza_id 
GROUP BY pn.pizza_name 

--What was the maximum number of pizzas delivered in a single order?
SELECT co.order_id, COUNT(*) AS counts 
FROM customer_orders co JOIN runner_orders ro
ON co.order_id = ro.order_id
WHERE ro.cancellation = ''
GROUP BY co.order_id
ORDER BY counts DESC
LIMIT 1;

--For each customer, how many delivered pizzas had at least 1 change and how many had no changes?
SELECT customer_id, changes, COUNT(*) AS counts
FROM 
	(SELECT co.customer_id, 
	CASE WHEN exclusions <> '' OR extras <> '' THEN 'Yes'
	ELSE 'No' 
	END changes
	FROM customer_orders co JOIN runner_orders ro
	ON co.order_id = ro.order_id
	WHERE ro.cancellation = '')
GROUP BY customer_id, changes;

--How many pizzas were delivered that had both exclusions and extras?
SELECT *
FROM customer_orders co INNER JOIN runner_orders ro 
ON co.order_id = ro.order_id 
WHERE ro.cancellation = '' AND co.exclusions <> '' AND co.extras <> '';

--What was the total volume of pizzas ordered for each hour of the day?
SELECT STRFTIME('%H', order_time) AS hours, COUNT(*)
FROM customer_orders co
GROUP BY hours;

--What was the volume of orders for each day of the week?
SELECT STRFTIME('%d', order_time) AS days, COUNT(*)
FROM customer_orders co
GROUP BY days;