customer_behavior_sql_queries.sql

USE customer_behavior;
GO

SELECT * FROM dbo.customer;

--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 customers 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 5 products with the highest average review rating?
select top 5 item_purchased, round(avg(review_rating), 2) as Avg_product_rating
from customer
group by item_purchased
order by avg(review_rating) desc

--Q4. Compare the average Purchase Amounts between Standard and Express Shipping. 
select shipping_type, round(avg(purchase_amount),2)
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_customer,
avg(purchase_amount) as avg_amount,
round(sum(purchase_amount),2) as total_amount
from customer
group by subscription_status
order by total_amount, avg_amount desc

--Q6. Which 5 products have the highest percentage of purchases with discounts applied?
select top 5 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

--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 (
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"
from customer_type
group by customer_segment
go

--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_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 repeat_buyers
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