customer_behavior_postgres.sql

select * from customer limit 20

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'customer';


--Q1. What is the total revenue generated by male vs. female customers?
SELECT "Gender", SUM("Purchase Amount (USD)") 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 (USD)"
FROM customer
WHERE "Discount Applied" = 'Yes'
  AND "Purchase Amount (USD)" >= (
      SELECT AVG("Purchase Amount (USD)")
      FROM customer
  );


--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 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 (USD)"),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 (USD)"),2) AS avg_spend,
       ROUND(SUM("Purchase Amount (USD)"),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? 
WITH age_buckets AS (
  SELECT
    CASE
      WHEN "Age" BETWEEN 18 AND 30 THEN 'Young Adult'
      WHEN "Age" BETWEEN 31 AND 45 THEN 'Adult'
      WHEN "Age" BETWEEN 46 AND 60 THEN 'Middle-Aged'
      ELSE 'Senior'
    END AS age_group,
    "Purchase Amount (USD)" AS purchase_amount_usd
  FROM customer
  WHERE "Age" IS NOT NULL
)
SELECT
  age_group,
  SUM(purchase_amount_usd) AS total_revenue
FROM age_buckets
GROUP BY age_group
ORDER BY total_revenue DESC;