customer_behavior_sql_queries.sql

-- Lets see my data at once 
SELECT * from customer;

-- 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), 2) AS `Average Product Rating`
FROM customer
GROUP BY item_purchased
ORDER BY AVG(review_rating) DESC
LIMIT 5;


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

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

-- Q6. Which 5 products have the highest percentage of purchases with discounts applied?
SELECT item_purchased,
       ROUND(100.0 * 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_counts 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_counts
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 total_revenue
FROM customer
GROUP BY age_group
ORDER BY total_revenue desc;

-- Customer Insights & Behavior
-- Q11. Who are the top 5 highest-spending customers, and what is their preferred payment method?
SELECT customer_id, 
       SUM(purchase_amount) AS total_spent,
       MAX(payment_method) AS preferred_payment -- Shows an associated payment method
FROM customer
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;

-- Q12. What is the average age and average purchase amount of customers who use promo codes versus those who do not?
SELECT promo_code_used,
       ROUND(AVG(age), 1) AS avg_age,
       ROUND(AVG(purchase_amount), 2) AS avg_purchase
FROM customer
GROUP BY promo_code_used;

-- Q13. Find the customer IDs of users whose purchase amount is strictly higher than the maximum purchase amount of 'New' customers (exactly 1 previous purchase).
SELECT customer_id, purchase_amount
FROM customer
WHERE purchase_amount > (
    SELECT MAX(purchase_amount) 
    FROM customer 
    WHERE previous_purchases = 1
);
 -- Product & Category Analysis
 
 -- Q14. Which categories generate the highest revenue, and what percentage of total company revenue does each category represent?
 SELECT category,
       SUM(purchase_amount) AS category_revenue,
       ROUND(100.0 * SUM(purchase_amount) / SUM(SUM(purchase_amount)) OVER(), 2) AS percentage_of_total_revenue
FROM customer
GROUP BY category
ORDER BY category_revenue DESC;
-- Q15. Find the most expensive item purchased within each category.
WITH ranked_products AS (
    SELECT category,
           item_purchased,
           purchase_amount,
           DENSE_RANK() OVER (PARTITION BY category ORDER BY purchase_amount DESC) AS price_rank
    FROM customer
)
SELECT category, item_purchased, purchase_amount
FROM ranked_products
WHERE price_rank = 1;

-- Q16. Identify "Low Rated but Popular" products: items with more than 10 total purchases but an average review rating below 4.0.
SELECT item_purchased,
       COUNT(*) AS total_orders,
       ROUND(AVG(review_rating), 2) AS avg_rating
FROM customer
GROUP BY item_purchased
HAVING COUNT(*) > 10 AND AVG(review_rating) < 4.0;

-- Shipping & Operational Metrics 

-- Q17. Which location (State) prefers Express shipping the most by volume?
SELECT location,
       COUNT(CASE WHEN shipping_type = 'Express' THEN 1 END) AS express_orders_count
FROM customer
GROUP BY location
ORDER BY express_orders_count DESC
LIMIT 5;

-- Q18. Compare the average review rating of products bought with discounts versus products bought at full price.
SELECT discount_applied,
       ROUND(AVG(review_rating), 2) AS average_rating,
       COUNT(*) AS total_reviews
FROM customer
GROUP BY discount_applied;


-- Advanced Segmentation & Patterns 

-- Q19. Calculate the difference between each customer's purchase amount and the average purchase amount for their specific age group.
SELECT customer_id,
       age_group,
       purchase_amount,
       ROUND(AVG(purchase_amount) OVER(PARTITION BY age_group), 2) AS age_group_avg,
       ROUND(purchase_amount - AVG(purchase_amount) OVER(PARTITION BY age_group), 2) AS spending_deviation
FROM customer;

-- Q20. Create a summary matrix showing the total number of orders for every combination of Season and Category.
SELECT season,
       category,
       COUNT(*) AS total_orders,
       SUM(purchase_amount) AS total_sales
FROM customer
GROUP BY season, category
ORDER BY season, total_sales DESC;