👗Glam Closing Co.

A company that commercializes stylish and trendy clothes.

Requirement

The sales manager requested a report including information on the sales department to analyze the sales performance to make business decisions.

Tools

Data package

The software to post downloaded transactions to a CSV file.

Database 'GlamClosing' with 3 tables: 'producto_detalle', 'producto_precio, 'ventas'.

CODING

Sales Aanalysis

#1. List sales quantities associated with all products SELECT SUM(qty) AS cantidad_total FROM GlamClosing.ventas;

#2. List sales quantities by products SELECT id_producto, SUM(qty) AS cantidad_total FROM GlamClosing.ventas GROPUP BY id_producto;

#3. List total income before discounts of all products sold SELECT FORMAT(SUM(precio*qty),2) AS ventas_brutas FROM GlamClosing.ventas;

#4. List total gross sales by products ID SELECT id_producto, SUM(precio*qty) AS ventas_brutas FROM GlamClosing.ventas GROUP BY by id_producto;

#5. List total net sales by products ID SELECT id_producto, SUM(precio*qty-descuento) AS ventas_netas FROM GlamClosing.ventas GROUP BY id_producto;

#6. List total gross sales and net sales by products ID SELECT id_producto, FORMAT(SUM(precio*qty),2) AS ventas_brutas, FORMAT(SUM(precio*qty-descuento),2) AS promedio_ventas_brutas FROM GlamClosing.ventas GROUP BY id_producto;

#7. List average gross sales before discounts by all products SELECT id_producto, FORMAT(SUM(precio*qty),2) AS ventas_brutas, FORMAT(AVG(precio*qty),2) AS ingreso_medio_ventas_brutas FROM GlamClosing.ventas GROUP BY id_producto;

#8. List gross sales before discounts by product SELECT pd.nombre_producto, FORMAT(SUM(v.precio*v.qty),2) AS ingreso_total, FORMAT(SUM(v.precio*v.qty-v.descuento),2) AS ingreso_neto FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle AS pd ON v.id_producto = pd.id_producto GROUP BY nombre_producto ORDER BY ingreso_neto desc;

#9. List total discounts to all products SELECT FORMAT(SUM(descuento),2) AS descuento_total FROM GlamClosing.ventas;

#10. List total discounts product SELECT pd.nombre_producto, FORMAT(SUM(v.precio*v.qty),2) AS ingreso_total, FORMAT(SUM(v.precio*v.qty-v.descuento),2) AS ingreso_neto, FORMAT(SUM(v.descuento),2) AS descuento_total FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle AS pd ON v.id_producto = pd.id_producto GROUP BY nombre_producto ORDER BY descuento_total desc;

Transaction Analysis

#1. List unique transactions. SELECT COUNT(DISTINCT(id_txn)) FROM GlamClosing.ventas;

#2. List total sales by transaction SELECT id_txn, FORMAT(SUM(precio*qty),2) AS ventas_brutas FROM GlamClosing.ventas GROUP BY id_txn ORDER BY ventas_brutas;

#3. List quantity of sold product by transaction SELECT id_txn, SUM(qty) AS cantidad_por_producto FROM GlamClosing.ventas GROUP BY id_txn ORDER BY cantidad_por_producto desc;

#4. Sort previous result from higher to lower product quantity SELECT id_txn, SUM(qty) AS cantidad_por_producto FROM GlamClosing.ventas GROUP BY id_txn ORDER BY cantidad_por_producto desc;

#5. List average discount by transaction SELECT id_txn, AVG(descuento) AS promedio_venta_bruta FROM GlamClosing.ventas GROUP BY id_txn ORDER BY promedio_venta_bruta desc;

#6. List average net income by transaction where member'c category = 't' SELECT id_txn, AVG(precio*qty-descuento) AS promedio_venta_neta FROM GlamClosing.ventas WHERE miembro = 't' GROUP BY id_txn ORDER BY promedio_venta_neta desc;

Product Analysis

#1. List top 3 products by total income before discount SELECT pd.nombre_producto, SUM(v.precio*v.qty) AS ventas_brutas FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY nombre_producto ORDER BY ventas_brutas desc LIMIT 3;

#2. List total quantity, total income, and total discount of eahc segment of product SELECT pd.nombre_segmento, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty-v.descuento) AS ventas_netas, SUM(v.descuento) AS descuento_total FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_segmento ORDER BY ventas_netas desc;

#3. List most sold product of each segment SELECT pd.nombre_segmento, pd.nombre_producto, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty-v.descuento) AS ventas_netas FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_segmento, pd.nombre_producto ORDER BY pd.nombre_segmento, ventas_netas desc;

#4. List total quantity, total income and total discount of each category SELECT pd.nombre_categoria, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty) AS ventas_brutas, SUM(v.descuento) AS descuento_total FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_categoria ORDER BY ventas_brutas desc;

#5. List most sold product of each category SELECT pd.nombre_categoria, pd.nombre_producto, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty-v.descuento) AS ventas_netas FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_categoria, pd.nombre_producto ORDER BY ventas_netas desc LIMIT 2;

#6. List top 5 lowest sold products SELECT pd.nombre_producto, SUM(v.qty) AS cantidad_total, SUM(v.precio*v.qty-v.descuento) AS ventas_netas FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto GROUP BY pd.nombre_producto ORDER BY ventas_netas LIMIT 5;

#7. List quantity sold products where category = 'Mujer' SELECT pd.nombre_categoria, SUM(v.qty) AS cantidad_total FROM GlamClosing.ventas AS v LEFT JOIN GlamClosing.producto_detalle pd ON v.id_producto = pd.id_producto WHERE pd.nombre_categoria = 'Mujer';

Outcomes