Project_customerbehavior.sql

use customer_behavior;

SELECT * FROM customer;

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

SELECT gender, SUM(purchase_amount) as revenue 
FROM customer
GROUP BY gender;

-- 2. 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);

-- 3. Which are the top 5 products with the highest average rating?
SELECT TOP 5 item_purchased, ROUND(AVG(CAST(review_rating AS DECIMAL(10,2))),2) AS [Average Product Rating]
FROM customer
GROUP BY item_purchased
ORDER BY AVG(CAST(review_rating AS DECIMAL(10,2))) DESC;

-- 4. Compare 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;

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

-- 6. Which 5 products have the highest percentage of purchases with discounts applied?
-- (which products heavily depend on discounts to sell)

SELECT Top 5 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;

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