projeto.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
);
-- data exploration
--count of rows

SELECT COUNT (*)
FROM zepto


--sample data
SELECT *
FROM zepto
LIMIT 10 ;

--null values
SELECT *
FROM zepto
WHERE NAME IS NULL
OR 
category 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

--diferentes categorias de produtos
SELECT  DISTINCT category
FROM zepto 
ORDER BY category;
--produtos estão em estoque ou fora de estoque
SELECT outofstock, COUNT (sku_id) AS total
FROM zepto
GROUP BY outofstock

- os nomes dos produtos são apresentados várias vezes
SELECT name, COUNT (sku_id) AS "NUMBER OF SKUs"
FROM zepto
GROUP BY name 
HAVING COUNT (sku_id) > 1
ORDER BY COUNT (sku_id) DESC;

- Limpeza de dados

-- Produtos com Preço = 0
SELECT *
FROM zepto
WHERE mrp = 0 OR discountedsellingprice = 0;

DELETE FROM zepto
WHERE mrp = 0;

-- converter paise em rúpias
UPDATE zepto
SET mrp = mrp/100.0,
discountedsellingprice = discountedsellingprice/100.0;

SELECT mrp, discountedsellingprice
FROM zepto

--01 encontre os 10 produtos de melhor valor com base na porcentagem de desconto
SELECT DISTINCT name, mrp, discountpercent
FROM zepto
ORDER BY discountpercent DESC
LIMIT 10

--02 quais são os produtos com alto MRP mas sem estoque
SELECT name, mrp
FROM zepto
WHERE outofstock = TRUE AND mrp > 300
ORDER BY mrp DESC;

--03 calcular estimado para cada categoria
SELECT category, SUM(discountedsellingprice * availablequantity) AS total_revenue
FROM zepto
GROUP BY category
ORDER BY total_revenue

--04 encontre todos os produtos onde o mrp é superior a US$ 500 e o desconto é inferior a 10%
SELECT DISTINCT name, mrp, discountPercent
FROM zepto
WHERE mrp > 500 AND discountPercent < 10
ORDER BY MRP DESC, discountPercent DESC;

--05 identificar as 5 principais categorias que oferecem a maior porcentagem média de desconto
SELECT DISTINCT category,ROUND(AVG(discountPercent),2) AS avg_discount
FROM zepto 
GROUP BY category
ORDER BY avg_discount DESC
LIMIT 5

--06 ENCONTRE O PREÇO POR GRAMA PARA PRODUTOS ACIMA DE 100G E CLASSIFIQUE POR MELHOR VALOR
SELECT DISTINCT name, weightingms, discountedsellingprice,ROUND(discountedsellingprice/weightingms,2) AS price_per_gram
FROM zepto
WHERE weightingms >= 100
ORDER BY  price_per_gram

--07 agrupar os produtos em categorias como baixo, médio, grande
  SELECT DISTINCT name, weightingms,
  CASE 
    WHEN weightInGms < 1000 THEN 'LOW'
    WHEN weightInGms BETWEEN 1000 AND 2000 THEN 'MEDIUM'
    ELSE 'HIGH'
  END AS weight_category
FROM zepto;

--08 qual é o peso total do estoque por categoria
SELECT category,
SUM(weightingms * availablequantity) AS total_weight
FROM zepto
GROUP BY category
ORDER BY total_weight