Customer_Behaviour_Analysis.sql

create database customer_behaviour;
use customer_behaviour;
show databases;
select * from customer limit 20;

alter table customer
change `Customer ID` customer_id int,
change `Age` age int,
change `Gender`  gender varchar(100),
change `Item Purchased` item_purchased varchar(100),
change `Category`  category varchar(50),
change `Purchase Amount (USD)` purchase_amount int, 
change `Location` location varchar(100),
change `Size` size varchar(10),
change `Color` color varchar(50),
change `Season` season varchar(20),
change `Review Rating` review_rating float,
change `Subscription Status` subscription_status varchar(100),
change `Shipping Type`  Shipping_type varchar(50),
change `Discount Applied` discount_applied varchar(100),
change `Promo Code Used` promo_code_used varchar(100),
change `Previous Purchases` previous_purchases int,
change `Payment Method` payment_method varchar(50), 
change `Frequency of Purchases` frequency_of_purchases varchar(100);

show columns from customer;


#1 what is the total revenue generated by male vs female customers- across demo
select sum('Purchase Amount(USD)') as "total_revenue"
from customer
group by gender;

#2 which customer use a discount but still spent more time than the average purchased amount
select Customer_id , purchase_amount from customer
where discount_applied='yes' and purchase_amount >=(select avg( purchase_amount) from customer);

#3 which are the top 5 products with the highest review rating
select item_purchased, round(avg(review_rating),2) as "average_review_rating"
from customer
group by item_purchased
order by avg(review_rating) desc
limit 5;

#4 which are the top 5 products with the highest average review rating 
select item_purchased , round(avg(review_rating),2)
from customer
group by item_purchased
order by avg (review_rating)
limit 5;
 
 
#5 compare the average purchase amount between standard and express shipping
select round(avg(purchase_amount),2) as "average_purchase_amount" , shipping_type
from customer
where shipping_type in ('Standard', 'Express')
group by shipping_type;

select shipping_type from customer;

#6 do subscribed customer spend more? 
#compare average spend and total revenue between subscribers and non-subscribers
select round(avg(purchase_amount),2) as "average spend", 
sum(purchase_amount) as "total revenue",subscription_status,
count(customer_id) as "total_customers"
from customer
group by subscription_status
order by total_revenue , average_spend desc;

select * from customer;

#7 which 5 products have the highest percentage of purchases with discount applied`
select item_purchased,
round(100* sum(case when 
discount_applied='yes' then 1 else 0 end )/count(*),2) as "discount_rate"
from customer
group by item_purchased
order by discount_rate desc
limit 5;

#8 segment customers into new,loyal,returning,
#based on their total number of previous purchases,and  show the count of each segment
with  customer_type as (select customer_id, previous_purchases,
case when previous_purchases=1 then 'new'
when previous_purchases between 2 and 10 then 'returning'
else 'loyal' end as customer_segment
from customer)
select customer_segment, count(*) as "number os customers"
from customer_type
group by customer_segment;

#9 what are the top 3 most purchased products in each category?
with item_counts as (select category,
item_purchased,count(customer_id) as total_orders,
row_number() over (partition by category order by count(customer_id) desc) as item_rank
from customer
group by category, item_purchased)

select item_rank,category, item_purchased, total_orders
from item_counts
where item_rank>=3;

#10 are customers who are repeat buyers (more than 5 previous purchases) also likely to subscribe?
select subscription_status,
count(customer_id) as repeat_buyers
from customer
where previous_purchases>5
group by subscription_status;

#11 what is the revenue contribution of each age group?
#which demographic is valuable for business
select age_group , sum(purchase_amount) as "revenue"
from 
(select purchase_amount,
case 
when age>60 then 'senior'
when age between 45 and 55 then 'middle_aged'
else 'young_adult' end as age_group
from customer) t
group by age_group
order by revenue desc;