Analysis with SQL.sql

-- 1. what is total revenue generated by male and female
select gender, sum(purchase_amount) as total_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 purchase_amount > (select avg(purchase_amount) from customer)
order by purchase_amount desc;

-- Q3. Which are the top 5 products with the highest average review rating?
select item_purchased , round(avg(review_rating)::numeric,2)
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, avg(purchase_amount)
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 case when subscription_status = 'Yes' then 'Subscribed' else 'Non-subscriber' end as "Subscription Status",
avg(purchase_amount) as "Avg Spend", sum(purchase_amount) as "Total Revenue"
from customer
group by case when subscription_status = 'Yes' then 'Subscribed' else 'Non-subscriber' end;

--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. 
select case when purchase_frequency_days between 1 and 90 then 'Returning' when purchase_frequency_days > 90 then 'Loyal' else 'New' end as "Customer Categories", count(*)
from customer
group by case when purchase_frequency_days between 1 and 90 then 'Retur
ning' when purchase_frequency_days > 90 then 'Loyal' else 'New' end;

-- same result with cte
with customer_type as (
select customer_id, previous_purchases, case when purchase_frequency_days between 1 and 10 then 'Returning' when purchase_frequency_days > 10 then 'Loyal' else 'New' end as "Customer Segment"
from customer) 
select "Customer Segment", count(*) as "Number of Customers"
from customer_type
group by "Customer Segment"; -- in most real world project cte is used

--Q8. What are the top 3 most purchased products within each category? 
with purchased_ranking as(
select category, item_purchased, count(item_purchased) as purchase_count,
row_number() over(partition by category order by count(item_purchased) desc)
from customer
group by category, item_purchased
)
select category, item_purchased, purchase_count
from purchased_ranking
where row_number < 4;

--Q9. Are customers who are repeat buyers (more than 5 previous purchases) also likely to subscribe?
select customer_id, previous_purchases, subscription_status
from customer
where previous_purchases > 5
order by previous_purchases desc;

--Q10. what is the revenue contribution of each group?
select age, sum(purchase_amount) as total_revenue
from customer
group by age
order by total_revenue;