Data_analysis.sql

Select * 
from customer
Limit 20;

-- Total revenue generated by male and female
select gender , sum(purchase_amount) as Revenue
from customer
group by gender;


-- which customers used a discont 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);


-- 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 2 desc
limit 5;

 
-- compare the avg purchase amount between standerd and express shipping


SELECT 
    shipping_type,
    ROUND(AVG(purchase_amount), 2) AS avg_purchase
FROM customer
WHERE shipping_type IN ('Express', 'Standard')
GROUP BY shipping_type;


-- another method
WITH Express_Type AS (
    SELECT ROUND(AVG(purchase_amount), 2) AS express_avg
    FROM customer
    WHERE shipping_type = 'Express'
),
Standard_Type AS (
    SELECT ROUND(AVG(purchase_amount), 2) AS standard_avg
    FROM customer
    WHERE shipping_type = 'Standard'
)

SELECT 
    s.standard_avg,
    e.express_avg
FROM Standard_Type s
CROSS JOIN Express_Type e;





-- do subscribed customers spend more? compare average spend and total revenue between subscribed and non subscreibed
select subscription_status ,count(customer_id),
 Round(avg(purchase_amount),2) as avg_spend,
 sum(purchase_amount) as total_revenue
from customer
group by 1
order by 3,2 desc;


-- which 5 product has the highest percentage of purchase with discount applied?
select item_purchased,
Round(sum(case when discount_applied = 'Yes' then 1 else 0 end)/count(*) *100,2) as `discount_rate %`
from customer
group by item_purchased
order by 2 desc
limit 5;



-- segment cust into new , returning and loyal based on their total number of pervious purchase , 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 1;


-- what are the top 3 most purchased products with in 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;




-- Are customers who are repeat buyers (more than 5 previous purchases ) also likely to subscribe ?

select count(customer_id)
from customer
where previous_purchases >= 5 and subscription_status = "Yes";


select subscription_status , count(customer_id) as `Repeat Buyers`
from customer
where previous_purchases > 5
group by 1;




-- revenue by age group 

select age_group , sum(purchase_amount) as `Total Revenue`
from customer
group by 1
order by 2 desc;