Bicycle Sales

-- Q1 How many orders are there? And what is the minimum, maximum and average revenue per order?
SELECT COUNT(DISTINCT oi.order_id) AS num_order, ROUND(MIN(oi.rev_per_order),2) AS min_rev, ROUND(MAX(oi.rev_per_order),2) AS max_rev, ROUND(AVG(oi.rev_per_order),2) AS avg_rev_per_order
FROM (
	SELECT order_id, SUM(quantity * list_price * (1 - discount)) AS rev_per_order
	FROM sales.order_items
	GROUP BY order_id
) AS oi;

-- 170 records with missing date in shipped_date
SELECT COUNT(*) AS num_missing 
FROM sales.orders
WHERE (shipped_date IS NULL)

-- Q2A How many orders were not shipped by the required date?
SELECT COUNT(*) AS num_late
FROM sales.orders
WHERE shipped_date > required_date;

-- Q2B How many days were they late by?
SELECT o.days_late, COUNT(*) AS freq
FROM (
	SELECT order_id, DATEDIFF(day, required_date, shipped_date) AS days_late
	FROM sales.orders
	WHERE shipped_date > required_date
) AS o
GROUP BY o.days_late;

-- Q2C Did the late orders contain a large quantity of items?
CREATE TABLE #order_quan(
	order_id INT,
	total_quan INT
);

INSERT INTO #order_quan
	SELECT order_id, SUM(quantity) AS total_quan
	FROM sales.order_items
	GROUP BY order_id;

SELECT in_time.total_quan, 
       in_time.freq_in_time, 
       late.freq_late, ROUND(100.0*late.freq_late/(in_time.freq_in_time + late.freq_late),2) AS percent_late
FROM (
	SELECT oi_sum.total_quan, COUNT(*) AS freq_in_time
	FROM sales.orders AS o
	LEFT JOIN #order_quan AS oi_sum
	ON o.order_id = oi_sum.order_id
	WHERE o.shipped_date <= o.required_date
	GROUP BY oi_sum.total_quan
) AS in_time
LEFT JOIN (
	SELECT l_oi_sum.total_quan, COUNT(*) AS freq_late
	FROM sales.orders AS l_o
	LEFT JOIN #order_quan AS l_oi_sum
	ON l_o.order_id = l_oi_sum.order_id
	WHERE l_o.shipped_date > l_o.required_date
	GROUP BY l_oi_sum.total_quan
) AS late
ON in_time.total_quan = late.total_quan;

DROP TABLE #order_quan;

-- Q3 How many staff were there in each store and how was the sales performance (measured by revenue)?
SELECT s.store_id, s.store_name, COUNT(DISTINCT st.staff_id) AS num_staff, os.store_rev
FROM sales.stores AS s
LEFT JOIN (
	SELECT o.store_id, ROUND(SUM(oi.rev_per_order),2) AS store_rev
	FROM sales.orders AS o
	LEFT JOIN (
		SELECT order_id, SUM(quantity*list_price*(1 - discount)) AS rev_per_order
		FROM sales.order_items
		GROUP BY order_id
	) AS oi
	ON o.order_id = oi.order_id
	GROUP BY o.store_id
) AS os
ON s.store_id = os.store_id
LEFT JOIN sales.staffs AS st
ON s.store_id = st.store_id
GROUP BY s.store_id, s.store_name, os.store_rev
ORDER BY os.store_rev DESC;

-- Q4 Get the employee-manager relationship. Who is the top manager?

WITH getManager AS (
	SELECT st.staff_id, st.first_name, st.last_name, st.store_id, st.manager_id, mg.first_name AS manager_first_name, mg.last_name AS manager_last_name, mg.store_id AS manager_store_id, 0 AS hierarchy_level
	FROM sales.staffs as st
	LEFT JOIN sales.staffs AS mg
	ON mg.staff_id = st.manager_id
	WHERE st.manager_id IS NULL

	UNION ALL

	SELECT st.staff_id, st.first_name, st.last_name, st.store_id, st.manager_id, mg.first_name, mg.last_name, mg.store_id, mg.hierarchy_level + 1 AS hierarchy_level
	FROM sales.staffs as st
	INNER JOIN getManager AS mg
	ON mg.staff_id = st.manager_id
	WHERE mg.hierarchy_level < 5
)

SELECT *
FROM getManager
ORDER BY hierarchy_level ASC;

-- Q5 What is the third most popular brand of bikes?
SELECT pb.brand_id, b.brand_name, pb.brand_total_quan
FROM production.brands AS b
RIGHT JOIN (
	SELECT p.brand_id, SUM(oi.total_quan) AS brand_total_quan, DENSE_RANK() OVER(ORDER BY SUM(oi.total_quan) DESC) AS rank
	FROM production.products AS p
	RIGHT JOIN (
		SELECT product_id, SUM(quantity) AS total_quan
		FROM sales.order_items
		GROUP BY product_id
	) AS oi
	ON p.product_id = oi.product_id
	GROUP BY p.brand_id
) AS pb
ON pb.brand_id = b.brand_id
WHERE pb.rank = 3;

-- Q6 What is the most popular brand in each bike category?
SELECT c.category_name, b.brand_name, summ.total
FROM (
	SELECT p.category_id, p.brand_id, SUM(oi.total_quan) AS total, DENSE_RANK() OVER(PARTITION BY p.category_id ORDER BY SUM(oi.total_quan) DESC) AS rank
	FROM production.products AS p
	RIGHT JOIN (
		SELECT product_id, SUM(quantity) AS total_quan
		FROM sales.order_items
		GROUP BY product_id
	) AS oi
	ON p.product_id = oi.product_id
	GROUP BY p.category_id, p.brand_id
) AS summ
LEFT JOIN production.categories AS c
ON c.category_id = summ.category_id
LEFT JOIN production.brands AS b
ON b.brand_id = summ.brand_id
WHERE summ.rank = 1;