customer_behavior_sql_queries.sql

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

SELECT gender, SUM(purchase_amount) AS revenue_generated
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 item_purchased,ROUND(AVG(review_rating::numeric),2) AS product_rating
FROM customer
GROUP BY item_purchased
ORDER BY product_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_customer,
ROUND(AVG(purchase_amount),2)AS average_spent, 
ROUND(SUM(purchase_amount),2) AS total_revenue 
FROM customer
GROUP BY subscription_status;


-- Q6. Which 5 products have the highest percentage of purchases with discounts applied?


SELECT 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 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 (
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(*)
FROM customer_type
GROUP BY customer_segment 

SELECT *
FROM customer

-- Q8. What are the top 3 most purchased products within each category?


WITH item_count 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_count
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)
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