da_mysql_queries.sql

--Q1. What is the total revenue generated by male vs. female customers?
select gender, SUM(purchase_amount) as revenue
from customers
group by gender

--Q2. Which customers used a discount but still spent more than the average purchase amount? 
select customer_id,discount_applied, purchase_amount 
from customers
where discount_applied = 'Yes' and purchase_amount >= (select AVG(purchase_amount) 
from customers
)

-- Q3. Which are the top 5 products with the highest average review rating?
select item_purchased, round(avg(review_rating::numeric),2) as "Average Product Rating"
from customers
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 customers
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 customers
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 * SUM(CASE WHEN discount_applied = 'Yes' THEN 1 ELSE 0 END)/COUNT(*),2) 
	   AS "discount_rate"
FROM customers
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::int = 1 THEN 'New'
            WHEN previous_purchases::int BETWEEN 2 AND 10 THEN 'Returning'
            ELSE 'Loyal'
        END AS customer_segment
    FROM customers
)
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(*) AS total_orders,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY COUNT(*) DESC
        ) AS item_rank 
    FROM customers
    GROUP BY category, item_purchased
)
SELECT item_rank,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 customers
WHERE previous_purchases::int > 5
GROUP BY subscription_status;


--Q10. What is the revenue contribution of each age group? 

SELECT 
    CASE
        WHEN age BETWEEN 18 AND 25 THEN 'Young-adult'
		WHEN age BETWEEN 18 AND 21 THEN 'Adult'
        WHEN age BETWEEN 40 AND 60 THEN 'Middle ages'
        WHEN age BETWEEN 60 AND 90 THEN 'Senior'
        WHEN age > 55 THEN 'Senior'
        ELSE 'Invalid'
    END AS age_group,
    SUM(purchase_amount) AS total_revenue
FROM customers
GROUP BY 1
ORDER BY total_revenue DESC;