analysis.sql

SELECT * FROM customer;

--what is the total revenue generated by male vs female customers
SELECT 
	gender,
	SUM(purchase_amount) AS total_revenue
FROM customer
GROUP BY 
	gender;

--which customers used discount but still spent more than average purchase amount
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 avg review rating
SELECT 
	item_purchased,
	ROUND(AVG(review_rating)) AS avg_review_rating
FROM customer
GROUP BY
	item_purchased
ORDER BY 
	ROUND(AVG(review_rating)) DESC 
LIMIT 5;

--compare purchase amount between standard and express shipping
SELECT 
	shipping_type,
	SUM(purchase_amount) as total_purchase_amount
FROM customer
WHERE 
	shipping_type = 'Express' OR shipping_type = 'Standard'
GROUP BY shipping_type;

--compare avg spend and total revenue between subscribers and non-subscribers
SELECT 
	CASE
		WHEN subscription_status = 'Yes' THEN 'subscribers'
		ELSE 'non-subscribers'
	END AS subscription_status,
	SUM(purchase_amount) AS total_revenue,
	ROUND(AVG(purchase_amount),2) AS average_spend
FROM customer
GROUP BY
	subscription_status;

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

--segemnt customers into new, returning and loyal based on their previous total purchases and show count of purchase
WITH customer_segment AS
(SELECT 
	customer_id,
	CASE
		WHEN previous_purchases = 1 THEN 'new'
		WHEN previous_purchases BETWEEN 2 AND 10 THEN 'returning'
		ELSE 'loyal'
	END AS customer_type
FROM customer)
SELECT 
	customer_type, 
	COUNT(customer_type) AS total_customers
FROM customer_segment
GROUP BY
	customer_type
;


--top 3 most purchased items within each category
WITH purchase_segment AS
(SELECT 
	item_purchased,
	category,
	COUNT(item_purchased) AS total_items,
	ROW_NUMBER () OVER(PARTITION BY category ORDER BY COUNT(item_purchased) DESC) AS ranking
FROM customer
GROUP BY 
	item_purchased,
	category
)
SELECT 
	item_purchased,
	category,
	total_items
FROM purchase_segment
WHERE 
	ranking <=3
ORDER BY category ASC, total_items DESC
;

--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
;
	
--revenue contribution of each age group
SELECT 
	age_group,
	SUM(purchase_amount) AS total_revenue
FROM customer
GROUP BY 	
	age_group;