sql zepto_project.sql

-- ============================================= -- 
-- create a database-- 
-- ============================================= --  

create database zepto_inventery;

-- ============================================= -- 
-- create a table-- 
-- ============================================= -- 
-- create table zepto_details(
-- sku_id serial primary key,
-- Category varchar(120),
-- name varchar(150) not null,
-- mrp numeric(8,2),
-- discountPercent numeric(5,2),
-- avaialbleQuantity integer,
-- discountedSellingPrice numeric(8,2),
-- weightinGms integer,
-- outOfStock boolean,
-- quantity integer
-- );

-- or
-- create auto table while importing data-- 
select * from zepto_v2;

-- ============================================= -- 
-- adding primary key column--
-- ============================================= -- 
select * from zepto_v2;

alter table zepto_v2
add column id int auto_increment primary key first;

-- ============================================= -- 
--  check all rows available
-- ============================================= -- 

select count(*)
from zepto_v2;

-- ============================================= -- 
-- sample data limit 10 -- 
-- ============================================= --
select * from zepto_v2 limit 10;

ALTER TABLE zepto_v2
CHANGE COLUMN `Category` category VARCHAR(120);

-- ============================================= -- 
-- check null values--
-- ============================================= --
 
select *
from zepto_v2
where category is null or
 name is null or
 mrp is null or
 discountPercent is null or
 availableQuantity is null or
 discountedSellingPrice is null or
 weightInGms is null or
 outOfStock is null or
 quantity is null;
 
-- ============================================= -- 
 -- check price  is 0--
-- ============================================= --
 select * from zepto_v2
 where mrp =0 or
 discountedSellingPrice=0;
 
 delete from zepto_v2
 where mrp =0 or
 discountedSellingPrice=0;
 
 -- ============================================= -- 
--  different product category (unique)-- 
-- ============================================= --
select distinct category
from zepto_v2
order by category;

-- ============================================= -- 
-- check how many products are avaialble and outofstock-- 
-- ============================================= --
select outOfStock, count(*)
from zepto_v2
group by outOfStock;

-- ============================================= -- 
-- different type of products--
-- ============================================= --
select name
from zepto_v2
group by name;

-- ============================================= -- 
--  product names present multiple times-- 
-- ============================================= --

select name, count(id) as 'total count'
from zepto_v2
group by name
having count(id) >1
order by count(id);

-- ============================================= -- 
-- convert paise to rupees-------------- 
-- ============================================= --

update zepto_v2
set mrp=mrp/100.0,
discountedSellingPrice=discountedSellingPrice/100.0;

select * from zepto_v2;

-- ============================================= -- 
-- business insights-- 
-- ============================================= --

-- top 10 products based on discount%-------
select distinct name ,mrp, discountpercent
from zepto_v2
order by discountPercent desc
limit 10;

-- products with high mrp but out of stock-- 
select  name, mrp
from zepto_v2
where outOfStock=true and mrp > 300
order by mrp desc;

describe zepto_v2;



update zepto_v2
set outofstock=
case when outOfstock='true' then 1
	 else 0
     end ;
     
     
alter table zepto_v2
modify outOfStock boolean;
   
select  name, mrp, outOfStock
from zepto_v2
where outOfStock=true 
order by mrp desc
limit 10;
     
-- calculate revenue for eachcategory--      
select category,
	sum(discountedsellingprice*availablequantity) as 'total revenue'
    from zepto_v2
    group by category;
    
-- find products mrp >500 and < 10% discount-- 
select name, mrp, discountPercent
from zepto_v2
where mrp >500 and discountPercent<10
order by mrp desc, discountPercent desc;

-- select top 5 category offering high discount % -- 
select category, 
round(avg(discountpercent),2) as 'avg_discount'
from zepto_v2
group by category
order by 'avg_discount' desc
limit 5;

-- find price per gram for products above 100g and sort by best value -- 
select name,discountedSellingPrice,(discountedSellingPrice/weightInGms) as'price_per_gram'
from zepto_v2
where weightInGms >100
order by 'price_per_gram' ;

-- group the products into category like low, medium, high-- 

select max(weightingms) from zepto_v2;

select category, name, weightInGms,
	case when weightIngms <1000 then 'low'
		 when weightingms <5000 then 'medium'
         else 'bulk'
         end as 'weight_category'
	from zepto_v2;
    
-- total inventery weight per category-- 

select category ,sum(weightingms*availableQuantity) as 'total_weight'
from zepto_v2
group by category
order by 'total_weight' desc;