customer_sql.sql

SELECT * FROM my_cleaned_customer LIMIT 5;

--Q1. What is the total revenue generated by male vs. female customer?

select gender, SUM(purchase_amount) as revenue
from my_cleaned_customer
group by gender

--Q2. Which ustomer used discount but spend more than the average purchase amount? 
select customer_id, purchase_amount
from my_cleaned_customer
where discount_applied = 'Yes' and purchase_amount >= (select avg(purchase_amount) from my_cleaned_customer)

-- Q3. Which are the top 5 products with highest average ratings

select item_purchased, ROUND(AVG(review_rating::numeric), 2) as avg_product_rating
from my_cleaned_customer
group by item_purchased
order by avg_product_rating desc
limit 5

--Q4. Compare the average purchase amount between standard and express shipping

select shipping_type, ROUND(avg(purchase_amount), 2) as avg_purchase_amount
from my_cleaned_customer
where shipping_type in ('Standard', 'Express')
group by shipping_type
order by avg_purchase_amount desc

--Q5. Do subscriber cutomer spend more? compare average spend and total revenue between subcriber and non subs criber

select subscription_status,
count(customer_id),
ROUND(avg(purchase_amount), 2) as avg_spending,
ROUND(sum(purchase_amount), 2) as total_revenue
from my_cleaned_customer
group by subscription_status
order by avg_spending, total_revenue desc

--Q6. Which 5 products has the highest percentage pf purchase with discount applied

select item_purchased, 
ROUND(sum(purchase_amount), 2) as total_revenue,
ROUND(100 * SUM(CASE WHEN discount_applied = 'Yes' then 1 else 0 end)/count(*), 2) as discount_rate
from my_cleaned_customer
group by item_purchased
order by total_revenue desc, discount_rate desc

--Q7. Segemnt customer into New, Returning, Loyal based on their total number of prevoius purchase, 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 my_cleaned_customer
)

select customer_segment, COUNT(*) as Number_of_customer 
from customer_type
group by customer_segment

-- Q8. What are the top 3 most purchase product witin each category?

with item_count 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 my_cleaned_customer
   group by category, item_purchased
)

select item_rank, category, item_purchased, total_orders
from item_count
where item_rank <= 3

--Q9. Are coustomer who are repeat buyers (more than 5) also likely to subscribe?

select subscription_status, count(customer_id) as total_customer
from my_cleaned_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 my_cleaned_customer
group by age_group
order by total_revenue desc