Customer_Behaviour_SQL_Queries.sql

-- create database customer_behaviour;
use customer_behaviour;

-- Q1. What is the total revenue generated by male vs. female customers?
select gender, sum(purchase_amount) as revenue
from customer_data
group by gender;

-- Q2. Which customers used a discount but still spent more than the average purchase amount?
select customer_id, purchase_amount
from customer_data
where discount_applied = "Yes" and purchase_amount >= (select avg(purchase_amount) from customer_data);

-- Q3. Which are the top 5 products with the highest average review rating?
select item_purchased, round(avg(review_rating),3) as avg_review_rating
from customer_data
group by item_purchased
order by avg_review_rating desc
limit 5;

-- Q4. Compare the average Purchase Amounts between Standard and Express Shipping.
select shipping_type, round(avg(purchase_amount),2) as avg_purchase_amount
from customer_data
where shipping_type in ("Express", "Standard")
group by shipping_type;

-- Q5. Do subscribed customers spend more? 
-- Compare average spend and total revenue between subscribers and non-subscribers.
select subscription_status, count(customer_id) as total_customers,
round(avg(purchase_amount),2) as average_spend, round(sum(purchase_amount),2) as total_revenue
from customer_data
group by subscription_status
order by total_revenue desc;

-- Q6. Which 5 products have the highest percentage of purchases with discounts applied?
-- Which products rely heavily on discounts to sell
select item_purchased, 
round(100 * sum(case when discount_applied = "Yes" then 1 else 0 end) / count(*),2) as percentage_purchase
from customer_data
group by item_purchased
order by percentage_purchase desc
limit 5;

-- Q7. Segment customers into New, Returning, and Loyal based on their total number of previous purchases, 
-- and show the count of each segment.
with customer_type as   -- CTE (Common Table Expression)
	(select customer_id, previous_purchases,
	(case
		when previous_purchases <= 1 then "New"
		when previous_purchases > 1 and previous_purchases <= 10 then "Returning"
		else "Loyal"
		end) as customer_segment
	from customer_data)
select customer_segment, count(*) as no_of_customers
from customer_type
group by customer_segment;

-- Q8. What are the top 3 most purchased products within 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_data
group by category, item_purchased
)
select item_rank, category, item_purchased, total_orders
from item_counts
where item_rank <= 3;

-- Q9. 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_data
where previous_purchases > 5
group by subscription_status;

-- Q10. What is the revenue contribution of each age group?
select age_group, sum(purchase_amount) as total_revenue
from customer_data
group by age_group
order by total_revenue desc;