Customer_behavior_SQL.sql

Select * from customer limit 20

--Q1 what is the total revenue generated by male vs. female customers?
Select gender, SUM(purchase_amount) as revenue from customer group by gender

--Q2 which customer used a discount but still spent more than the average purchase amount?
select customer_id, purchase_amount from customer 
where discount_applied = 'Yes' and purchase_amount >= (select AVG(purchase_amount) from customer)

--Q3 which are the top highest products with the highest average review rating?
select item_purchased, AVG(review_rating) as "Average product Rating" from customer
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, AVG(purchase_amount) from customer
where shipping_type in ('Standard','Express') 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,
AVG(purchase_amount) as avg_spend, SUM(purchase_amount) as total_revenue from customer
group by subscription_status order by total_revenue, avg_spend desc

--Q6 which 5 product have the highest percentage of purchases with discounts applied?
select item_purchased,
100 * SUM(CASE WHEN discount_applied = 'Yes' THEN 1 ELSE 0 END) / COUNT(*) as Discount_rate
from customer
group by item_purchased
order by discount_rate desc
limit 5

--Q7 segment customers into New , returning, and loyal based on their total 
-- number of previous purchases, and show teh 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 of Customers he bhai yeh sb"
from customer_type
group by customer_segment

--Q8 what rae the top 3 most purchased products within each category?
with item_counts as (
select category, 
item_purchased,
Count(customer_id) as total_order,
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_order 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 repated_buyer
from customer
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
group by age_group
order by total_revenue desc