Workout Difficulty:
Questions:
Complete the workout here.
Simply post your code and a screenshot of your results.
Please format your Python code and blur it or place it in a hidden section.
This workout will be released on Monday May 29, 2023, and the author’s solution will be posted on Sunday June 4, 2023.
/*Which customer has made the most orders?*/
SELECT customer_id, COUNT(order_id) AS number_of_orders
FROM orders
GROUP BY customer_id
ORDER BY number_of_orders DESC
LIMIT 1;
/*What’s the total revenue per product?*/
SELECT
order_items.product_id,
SUM(order_items.quantity * products.price) AS total_revenue
FROM order_items
JOIN products
ON order_items.product_id = products.product_id
GROUP BY order_items.product_id;
/*Find the day with the highest revenue.*/
SELECT
orders.order_date,
SUM(order_items.quantity * products.price) AS total_revenue
FROM orders
JOIN order_items
ON orders.order_id = order_items.order_id
JOIN products
ON order_items.product_id = products.product_id
GROUP BY orders.order_date
ORDER BY total_revenue DESC
LIMIT 1;
– 1. Which customer has made the most orders?
select
customers.customer_id,
customers.first_name,
customers.last_name,
count(orders.order_id) as orders_per_customer
from customers
left join orders
on customers.customer_id = orders.customer_id
group by 1
order by 4 desc;
– 2. What’s the total revenue per product?
select
order_items.product_id,
products.product_name,
products.price,
sum(order_items.quantity) as ordered_quantity,
products.price*sum(order_items.quantity) as revenue_per_product
from order_items
left join products
on order_items.product_id = products.product_id
group by 1, 2
ORDER BY 5 DESC;
– 3. Find the day with the highest revenue.
select
virtual1.order_date,
sum(virtual1.revenue_per_product_per_day) as daily_revenue
from(
select
orders.order_date,
order_items.product_id,
products.product_name,
products.price,
sum(order_items.quantity) as ordered_quantity,
products.price*sum(order_items.quantity) as revenue_per_product_per_day
from order_items
left join products
on order_items.product_id = products.product_id
left join orders
on order_items.order_id = orders.order_id
group by 1, 2) as virtual1
group by 1
order by 2 desc;
-- Case Study Questions
-- 1. Which customer has made the most orders?
-- I choose method using subquery, other possible is with CTE or using rank() function
select customer_id, count(order_id) as total_orders
from orders
group by customer_id
having count(order_id) = (select top 1 count(order_id) from orders group by customer_id);
-- 2. What’s the total revenue per product?
-- basic join and sum() function
select p.product_id, sum(p.price * o.quantity) as total_revenue
from products p
inner join order_items o on p.product_id = o.product_id
group by p.product_id
order by total_revenue desc;
-- 3. Find the day with the highest revenue.
with cte as (select o.order_date, sum(p.price * oi.quantity) as day_revenue
from orders o
inner join order_items oi on o.order_id = oi.order_id
inner join products p on oi.product_id = p.product_id
group by o.order_date)
select order_date, day_revenue
from cte
where day_revenue = (select max(day_revenue) from cte);
Which customer has made the most orders?
select a.customer_id,COUNT(*) as No_of_orders,concat(b.first_name,' ',b.last_name) as Customer_name from orders a
inner join customers b on
a.customer_id=b.customer_id
group by a.customer_id,b.customer_id,b.first_name,b.last_name
What’s the total revenue per product?
with cte as (select P.*, O.order_id,O.quantity, price*quantity as Total from products P inner join order_items O
on P.product_id=O.product_id)
select product_id,product_name,SUM(Total) as Total_revenue_per_product
from cte
group by product_id,product_name
order by Total_revenue_per_product desc;
Find the day with the highest revenue.
with cte as (
select P.*, OI.order_id,OI.quantity, O.order_date, price*quantity as Total from products P
inner join order_items OI
on P.product_id=OI.product_id
inner join orders O
on O.order_id=OI.order_id)
select order_date, SUM(Total) as Highest_revnue_per_day
from cte
group by order_date
order by Highest_revnue_per_day desc;