SQL Queries.sql

CREATE DATABASE mydatabase;
USE mydatabase;
SHOW TABLES;
SELECT*FROM mytable LIMIT 10;

#Ques1. waht is the total revenue genereated by the male v/s female customer?
SELECT gender,sum(purchase_amount) as revenue FROM mytable
GROUP BY gender;

#Ques2. which customer used a discount but still spent more than the avg purchase_amount.
SELECT customer_id,purchase_amount FROM mytable
WHERE discount_applied='Yes' and purchase_amount>= (SELECT avg(purchase_amount) FROM mytable);

#Ques3. what are the top 5 products with the highest average review rating.
SELECT item_purchased,round(avg(review_rating)) as average_product_rating
FROM mytable 
group by item_purchased
order by avg(review_rating) DESC limit 5;

# Ques4. compare the average purchase amounts between standard and express shipping.
SELECT shipping_type ,ROUND(avg(purchase_amount),2)
FROM mytable 
WHERE shipping_type IN ('standard','express')
group by shipping_type;

#ques5. do subscribed spend more?  compare avergae spent and total revenue
# between subscibed and non-subscriber.
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 mytable
GROUP BY subscription_status
ORDER BY  avg_spend,total_revenue;

#Ques6. which 5 products have the highest percentage of purchases 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 mytable
GROUP BY item_purchased
ORDER BY discount_rate DESC 
limit 5;

#Ques 7. segment customer 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 1 and 10 THEN 'returning'
    ELSE 'loyal'
    END AS customer_segment
    from mytable
)
SELECT customer_segment,count(*) as 'number of customer'
from customer_type
group by customer_segment;
 
 #Ques8. what are 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 mytable 
 GROUP  BY item_purchased,category
 )
 SELECT item_rank,item_purchased,total_orders FROM item_counts 
 WHERE item_rank<=3;
 
 # Ques9 . Are customer who are repeat buyers (more than 5 previous  purchase )  also likely to subscribe.
 SELECT subscription_status,
 count(customer_id) as repeat_buyers 
 FROM mytable 
 WHERE previous_purchases>5
 group by subscription_status;
SELECT *FROM mytable LIMIT 10;

#Ques10 .what is the revenue contribution of each group
SELECT age_group,
SUM(purchase_amount) as total_revenue
FROM mytable 
GROUP BY age_group
ORDER by total_revenue 
DESC;