Zepto_SQL Project.sql

-- Create database and use it --
CREATE DATABASE sql_project;
USE sql_project;

-- Creation of Table --
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,	-- 0 for False an 1 for True --
quantity INTEGER
);

-- Print the table --
SELECT * FROM zepto;

-- Disable SAFE Mode --
SET SQL_SAFE_UPDATES = 0;

											-- DATA EXPLORATION --

-- count no. of rows --
select count(*)  AS Number_of_Rows 
from zepto;

-- Sample data --
SELECT * FROM zepto
LIMIT 5 ;

-- NULL values --
SELECT * FROM zepto
WHERE name IS NULL
OR
category IS NULL
OR
mrp IS NULL
OR
discountPercent IS NULL
OR
discountedSellingPrice IS NULL
OR
weightInGms IS NULL
OR
availableQuantity IS NULL
OR
outOfStock IS NULL
OR
quantity IS NULL;


-- Different Product Categories --
SELECT DISTINCT category 
FROM zepto
ORDER BY category ;

-- Products In Stock VS Out Of Stock --
SELECT outOfStock, COUNT(sku_id)
FROM zepto
GROUP BY outOfStock;


-- Product Names present multiple times--
SELECT name AS Product_Name , count(sku_id) AS "Number of SKUs"
FROM zepto
GROUP BY name
HAVING count(sku_id) > 1
ORDER BY count(sku_id) DESC;


											-- DATA CLEANING --
-- Product with Price = 0 --
SELECT * FROM zepto
WHERE mrp = 0 OR discountedSellingPrice = 0;

DELETE FROM zepto
WHERE mrp = 0;

-- Convert Paise To Rupees --
UPDATE zepto
SET mrp = mrp / 100.0,
discountedSellingPrice = discountedSellingPrice / 100.0;

SELECT mrp, discountedSellingPrice FROM zepto;

											-- DATA ANALYSIS --

-- Q1. Find the TOP 10 best-value products based on the discount percentage. --
SELECT DISTINCT name, mrp, discountPercent
FROM zepto
ORDER BY discountPercent DESC
LIMIT 10;

-- Q2. What are the Products with High MRP but Out of Stock. --
SELECT DISTINCT name,mrp
FROM zepto
WHERE outOfStock = 1 and mrp > 300 -- 1 means True --
ORDER BY mrp DESC;

-- Q3.Calculate Estimated Revenue for each category. --
SELECT category,
SUM(discountedSellingPrice * availableQuantity) AS total_revenue
FROM zepto
GROUP BY category
ORDER BY total_revenue;

-- Q4. Find all products where MRP is greater than ₹500 and discount is less than 10%. --
SELECT DISTINCT name, mrp, discountPercent
FROM zepto
WHERE mrp > 500 AND discountPercent < 10
ORDER BY mrp DESC, discountPercent DESC;

-- Q5. Identify the top 5 categories offering the highest average discount percentage. --
SELECT category,
ROUND(AVG(discountPercent),2) AS avg_discount
FROM zepto
GROUP BY category
ORDER BY avg_discount DESC
LIMIT 5;

-- Q6. Find the price per gram for products above 100g and sort by best value. --

SELECT DISTINCT name, weightInGms, discountedSellingPrice,
ROUND(discountedSellingPrice/weightInGms,2) AS price_per_gram
FROM zepto
WHERE weightInGms >= 100
ORDER BY price_per_gram;


-- Q7. Group the products into categories like Low, Medium, Bulk. --

SELECT DISTINCT name, weightInGms,
CASE WHEN weightInGms < 1000 THEN 'Low'
	WHEN weightInGms < 5000 THEN 'Medium'
	ELSE 'Bulk'
	END AS weight_category
FROM zepto;

-- Q8. What is the Total Inventory Weight Per Category. --

SELECT category,
SUM(weightInGms * availableQuantity) AS total_weight
FROM zepto
GROUP BY category
ORDER BY total_weight;