Zepto_SQL.sql

drop table if exists zepto;

create table zepto (
sku_id SERIAL PRIMARY KEY,
category VARCHAR(120),
name VARCHAR(150) NOT NULL,
mrp NUMERIC(8,2),
discountPercent NUMERIC(5,2),
availableQuantity INTEGER,
discountedSellingPrice NUMERIC(8,2),
weightInGms INTEGER,
outOfStock BOOLEAN,	
quantity INTEGER
);

-- View all rows
SELECT * FROM zepto;

-- Count total records
SELECT COUNT(*) FROM zepto;

--Q1. Total Revenue (MRP & Selling Price) per Category
select 
	Category,
	sum(mrp) as Total_mrp,
	sum(discountedsellingprice) as Total_selling_price
from zepto
group by category
order by Total_mrp desc;

--Q2. Average Discount Percent per Category
select 
	Category,
	round(avg(discountpercent),2) as discount_percentage
from zepto
group by category
order by discount_percentage desc;

--Q3. Top 3 Most Expensive Items per Category
select * from
	(select Category, name, mrp, 
           rank() over (partition by Category order by mrp desc) as rnk
from zepto)
where rnk <=3;

--Q4. Running Total of Available Quantity
SELECT Category, name, mrp, availableQuantity,
       SUM(availableQuantity) OVER (PARTITION BY Category ORDER BY mrp) as running_total
FROM zepto;

--Q5. Categories with Above-Average Discounts
WITH OverallAvg AS (
    SELECT AVG(discountPercent) as global_avg FROM zepto
),
CategoryAvg AS (
    SELECT Category, AVG(discountPercent) as cat_avg FROM zepto GROUP BY Category
)
SELECT * FROM CategoryAvg 
JOIN OverallAvg ON CategoryAvg.cat_avg > OverallAvg.global_avg;

--Q6. Price Difference from Category Average
SELECT name, mrp,
       AVG(mrp) OVER (PARTITION BY Category) as cat_avg_mrp,
       mrp - AVG(mrp) OVER (PARTITION BY Category) as diff_from_avg
FROM zepto;

--Q7. Stock Availability Count
SELECT 
    Category,
    SUM(CASE WHEN outOfStock = 'FALSE' THEN 1 ELSE 0 END) AS In_Stock,
    SUM(CASE WHEN outOfStock = 'TRUE' THEN 1 ELSE 0 END) AS Out_of_Stock
FROM zepto_products
GROUP BY Category;

--Q8."Premium" Items Identification
WITH Stats AS (
    SELECT Category, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY mrp) as p90
    FROM zepto GROUP BY Category
)
SELECT z.name, z.mrp 
FROM zepto z 
JOIN Stats s ON z.Category = s.Category 
WHERE z.mrp > s.p90;