customer_behavior.sql

-- total revenue generated by male and female 
SELECT gender, SUM(purchase_amount) AS revenue
FROM customer
GROUP BY gender;

-- cutomer who spent more than average purchase amount even with discount bill
SELECT customer_id, purchase_amount
FROM customer
WHERE discount_applied = 'Yes' AND purchase_amount >=(SELECT AVG(purchase_amount) FROM customer);

-- top 5 products with the highest average review rating
SELECT item_purchased, ROUND(AVG(review_rating), 2) AS 'Average Product Rating'
FROM customer
GROUP BY item_purchased
ORDER BY AVG(review_rating) DESC
LIMIT 5;

-- comapring 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;

-- comparing 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 avg_spend, 
ROUND(SUM(purchase_amount), 2) AS total_revenue 
FROM customer
GROUP BY subscription_status
ORDER BY total_revenue, avg_spend;

-- 5 products have the highest percentage of purchases with discounts applied
SELECT item_purchased,
ROUND(SUM(CASE WHEN discount_applied = 'Yes' THEN 1 ELSE 0 END) /COUNT(*) *100, 2) AS discount_rate
FROM customer
GROUP BY item_purchased
ORDER BY discount_rate DESC
LIMIT 5;

-- 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;
 
-- top 3 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
GROUP BY category, item_purchased
)
SELECT item_rank, category, item_purchased, total_orders
FROM item_counts
WHERE item_rank <= 3;

-- repeated buyers (more than 5 previous purchases) are subscribing / not subsrcribing
SELECT subscription_status,
COUNT(customer_id) AS repeat_buyers
FROM customer
WHERE previous_purchases > 5
GROUP BY subscription_status;

-- revenue contribution of each age group
SELECT age_group, SUM(purchase_amount) AS revenue
FROM customer
GROUP BY age_group
ORDER BY revenue DESC;