business_queries.sql

-- =====================================================
-- E-Commerce Sales Analysis using MySQL
-- Author : Vikash Basfore
-- Description:
-- Collection of SQL queries used for business analysis
-- =====================================================

USE ecommerce;

-- =====================================================
-- Question 1
-- List all unique cities where customers are located
-- =====================================================

SELECT DISTINCT customer_city
FROM customers;


-- =====================================================
-- Question 2
-- Count the number of orders placed in 2017.
-- =====================================================

Select count(order_id)  from orders
where year(order_purchase_timestamp) = 2017 


-- =====================================================
-- Question 3
-- Find the total sales per category.
-- =====================================================

select upper(p.product_category) as category, 
round(sum(py.payment_value),2) sales
from products p join order_items oi
on p.product_id = oi.product_id
join payments py
on py.order_id = oi.order_id
group by category


-- =====================================================
-- Question 4
-- Calculate the percentage of orders that were paid in installments
-- =====================================================

select (SUM(case when payment_installments >= 1 then 1
else 0 end)) / count(*) * 100 from payments; 


-- =====================================================
-- Question 5
-- Count the number of customers from each state.
-- =====================================================

select customer_state, count(customer_id)
from customers
group by customer_state;


-- =====================================================
-- Question 6
-- Calculate the number of orders per month in 2018.
-- =====================================================
Select monthname(order_purchase_timestamp) months, count(order_id) order_counts
from orders
where year(order_purchase_timestamp) = 2018
group by months;


-- =====================================================
-- Question 7
-- Find the average number of products per order, grouped by customer city.
-- =====================================================

with count_per_order as
(select o.order_id, o.customer_id, count(oi.order_id) as oc # order_count
from orders o join order_items oi
on o.order_id = oi.order_id
group by o.order_id, o.customer_id)

select c.customer_city, round(avg(count_per_order.oc),2) average_orders
from customers c join count_per_order
on c.customer_id = count_per_order.customer_id
group by c.customer_city
order by average_orders desc;


-- =====================================================
-- Question 8
-- Calculate the percentage of total revenue contributed by each product category.
-- =====================================================

select upper(p.product_category) as category, 
round((sum(py.payment_value) / (Select sum(payment_value) from payments))*100, 2) sales_percentage
from products p join order_items oi
on p.product_id = oi.product_id
join payments py
on py.order_id = oi.order_id
group by category
order by sales_percentage desc;


-- =====================================================
-- Question 9
-- Identify the correlation between product price and the number of times a product has been purchased.
-- =====================================================

Select p.product_category,
count(oi.product_id),
round(avg(oi.price),2)
from products p join order_items oi
on p.product_id = oi.product_id
group by p.product_category;


-- =====================================================
-- Question 10
-- Calculate the total revenue generated by each seller, and rank them by revenue.
-- =====================================================

select *, dense_rank() Over(order by revenue desc) as rn from 
(select oi.seller_id, sum(p.payment_value) revenue
from order_items oi join payments p
on oi.order_id = p.order_id
group by oi.seller_id) as a;


-- =====================================================
-- Question 11
-- Calculate the moving average of order values for each customer over their order history.
-- =====================================================
select customer_id, order_purchase_timestamp, payment,
avg(payment) over(partition by customer_id order by order_purchase_timestamp
rows between 2 preceding and current row) as mov_avg
from
(select o.customer_id, o.order_purchase_timestamp, 
p.payment_value as payment
from payments p join orders o
on p.order_id = o.order_id) as a;


-- =====================================================
-- Question 12
-- Calculate the cumulative sales per month for each year.
-- =====================================================

Select years, months, payment,
round(sum(payment) over(order by years, months),2) as cumulative_sales from 
(Select year(o.order_purchase_timestamp) as years, 
month(o.order_purchase_timestamp) as months,
round(sum(p.payment_value),2) as payment
from orders o join payments p
on o.order_id = p.order_id
group by years , months
order by years, months) as a;


-- =====================================================
-- Question 13
-- Calculate the year-over-year growth rate of total sales.
-- =====================================================

with a as
(Select year(o.order_purchase_timestamp) as years, 
round(sum(p.payment_value),2) as payment
from orders o join payments p
on o.order_id = p.order_id
group by years
order by years)

select years, ((payment - lag(payment, 1) over(order by years)) / 
lag(payment, 1) over(order by years))* 100
 previous_year from a;


-- =====================================================
-- Question 14
-- Calculate the retention rate of customers, defined as the percentage of customers who make another purchase within 6 months of their first purchase
-- =====================================================

with a as
(
select c.customer_id, min(o.order_purchase_timestamp) first_order
from customers c join orders o
on c.customer_id = o.customer_id
group by c.customer_id),

b as (select a.customer_id, count(Distinct o.order_purchase_timestamp) next_order
from a join orders o
on o.customer_id = a.customer_id
and o.order_purchase_timestamp > first_order
and o.order_purchase_timestamp < date_add(first_order, interval 6 month)
group by a.customer_id)

select 100* (count(distinct a.customer_id) / count(distinct b.customer_id))
from a left join b on a.customer_id = b.customer_id;


-- =====================================================
-- Question 15
-- Identify the top 3 customers who spent the most money in each year.
-- =====================================================

 select years, customer_id, payment, D_rank from 
(
select year(o.order_purchase_timestamp) years,
o.customer_id, 
round(sum(p.payment_value),2) payment,
dense_rank() over(partition by year(o.order_purchase_timestamp) 
order by sum(p.payment_value) desc) D_rank
from orders o join payments p
on p.order_id = o.order_id
group by year(o.order_purchase_timestamp),	 o.customer_id) as a
where D_rank <= 3;