customer_behavior_sql_queries.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 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 "average_review_rating"
FROM customer
GROUP BY item_purchased
ORDER BY average_review_rating DESC
LIMIT 5;

--Q4. Compare the average Purchase Amounts between Standard and Express Shipping.

SELECT shipping_type, 
ROUND(AVG(purchase_amount::numeric), 2) as average_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-sub.

SELECT subscription_status, 
COUNT(customer_id) AS "total_customers",
ROUND(AVG(purchase_amount::numeric), 2) as avg_spend,
ROUND(SUM(purchase_amount::numeric), 2) as total_revenue
FROM customer
GROUP BY subscription_status
ORDER BY total_revenue DESC, avg_spend DESC;

--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(*) AS "Number of Customers"
FROM customer_type
GROUP BY customer_segment;

--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_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_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) 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 revenue_contribution
FROM customer
GROUP BY age_group
ORDER BY revenue_contribution DESC;