answers.sql

USE customer_trends_data_analysis;
select * from customer_data limit 15;

-- Business Questions : 

-- Q1. What are the total revenue generated by male vs female customers?
SELECT 
gender, SUM(purchase_amount_usd) 
AS total_revenue 
FROM customer_data 
group by gender;

-- Q2. Which customer used a discount but still spent more than the average purchase amount?
SELECT 
customer_id, purchase_amount_usd
FROM customer_data
WHERE discount_applied = "YES"
AND purchase_amount_usd > (
	SELECT AVG(purchase_amount_usd) FROM customer_data
);

-- Q3. Which are the top 5 products with the highest average review rating?
SELECT 
item_purchased, ROUND(AVG(review_rating),2) AS "Average Product Rating"
FROM customer_data 
GROUP BY item_purchased
ORDER BY AVG(review_rating) DESC
LIMIT 5;

-- Q4. Compare the average purchase amount between Standard and Express Shipping?
SELECT 
shipping_type, ROUND(AVG(purchase_amount_usd),2) AS 'Average Purchased Amount'
FROM customer_data
WHERE shipping_type in ('Express','Standard')
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_customers",
ROUND(AVG(purchase_amount_usd),2) AS "average_spend", 
SUM(purchase_amount_usd) AS "total_revenue"
FROM customer_data 
GROUP BY subscription_status
ORDER BY total_revenue, average_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_data
GROUP BY item_purchased
ORDER BY discount_rate DESC
LIMIT 5;

-- Q7. Segment customers into new, returning and loyal based on their total no of previous purchases and shoe the ocunt 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_data
)

SELECT 
    customer_segment,
    COUNT(*) AS total_customers
FROM customer_type
GROUP BY customer_segment;

-- Q8. What are the top 3 most purchased products within each category?
WITH item_counts AS (
    SELECT 
        item_purchased,
        category,
        COUNT(customer_id) AS total_orders,
        ROW_NUMBER() OVER(
            PARTITION BY category
            ORDER BY COUNT(customer_id) DESC
        ) AS item_rank
    FROM customer_data
    GROUP BY category, item_purchased
)

SELECT 
item_rank,
category,
item_purchased,
total_orders
FROM item_counts
WHERE item_rank <= 3;

-- Q9. Are customers who are repeated buyers (more than 5 previous purchases) also likely to subscribe?
SELECT 
subscription_status,
COUNT(customer_id) AS repeated_customers
FROM customer_data 
WHERE previous_purchases > 5
GROUP BY subscription_status;

-- Q10. What is the revenue contribution of each age group?
SELECT 
age_group, 
SUM(purchase_amount_usd) AS total_revenue
FROM customer_data
GROUP BY age_group
ORDER BY total_revenue DESC;