Skip to content
Project: Analyzing and Formatting PostgreSQL Sales Data

Cleaning a PostgreSQL Database

In this project, you will work with data from a hypothetical Super Store to challenge and enhance your SQL skills in data cleaning. This project will engage you in identifying top categories based on the highest profit margins and detecting missing values, utilizing your comprehensive knowledge of SQL concepts.

Data Dictionary:

orders:

ColumnDefinitionData typeComments
row_idUnique Record IDINTEGER
order_idIdentifier for each order in tableTEXTConnects to order_id in returned_orders table
order_dateDate when order was placedTEXT
marketMarket order_id belongs toTEXT
regionRegion Customer belongs toTEXTConnects to region in people table
product_idIdentifier of Product boughtTEXTConnects to product_id in products table
salesTotal Sales Amount for the Line ItemDOUBLE PRECISION
quantityTotal Quantity for the Line ItemDOUBLE PRECISION
discountDiscount applied for the Line ItemDOUBLE PRECISION
profitTotal Profit earned on the Line ItemDOUBLE PRECISION

returned_orders:

ColumnDefinitionData type
returnedYes values for Order / Line Item ReturnedTEXT
order_idIdentifier for each order in tableTEXT
marketMarket order_id belongs toTEXT

people:

ColumnDefinitionData type
personName of Salesperson credited with OrderTEXT
regionRegion Salesperson in operating inTEXT

products:

ColumnDefinitionData type
product_idUnique Identifier for the ProductTEXT
categoryCategory Product belongs toTEXT
sub_categorySub Category Product belongs toTEXT
product_nameDetailed Name of the ProductTEXT

As you can see in the Data Dictionary above, date fields have been written to the orders table as TEXT and numeric fields like sales, profit, etc. have been written to the orders table as Double Precision. You will need to take care of these types in some of the queries. This project is an excellent opportunity to apply your SQL skills in a practical setting and gain valuable experience in data cleaning and analysis. Good luck, and happy querying!

Spinner
DataFrameavailable as
top_five_products_each_category
variable
-- top_five_products_each_category
WITH cte AS (
	SELECT 
		products.category,
		products.product_name,
		SUM(orders.sales) AS product_total_sales,
		SUM(orders.profit) AS product_total_profit,
		RANK() OVER(PARTITION BY products.category ORDER BY SUM(orders.sales) DESC) AS product_rank
	FROM
		orders 
	JOIN 
		products 
	ON 
		products.product_id = orders.product_id
	GROUP BY 
		category, product_name
	)
SELECT 
	category,
	product_name,
	ROUND(product_total_profit::NUMERIC, 2) AS total_profit,
	ROUND(product_total_sales::NUMERIC, 2) AS total_sales,
	product_rank
FROM
	cte
WHERE 
	product_rank <= 5
ORDER BY 
	category ASC, product_total_sales DESC;
Spinner
DataFrameavailable as
salesperson_market_sales_details
variable
-- salesperson_market_sales_details
SELECT 
	people.person,
	orders.market,
	CASE WHEN orders.sales >= 0 and orders.sales < 100 THEN '0-100'
	WHEN orders.sales >= 100 and orders.sales < 500 THEN '100-500'
	ELSE '500+' END AS sales_bin,
	COUNT(DISTINCT orders.order_id) AS order_counts,
	SUM(CASE WHEN returned_orders.order_id IS NULL THEN 0 ELSE 1 END) AS orders_returned,
	SUM(orders.sales) AS total_sales,
	SUM(CASE WHEN returned_orders.order_id IS NULL THEN 0 
		ELSE orders.sales END) AS returned_sales
FROM 
	people 
JOIN 
	orders 
ON people.region = orders.region 
LEFT JOIN 
	returned_orders 
ON returned_orders.order_id = orders.order_id and returned_orders.market = orders.market
GROUP BY 
	people.person, orders.market, sales_bin
ORDER BY 
	people.person, orders.market, sales_bin;
Spinner
DataFrameavailable as
impute_missing_values
variable
-- impute_missing_values
-- WITH cte AS ( 
-- 	SELECT product_id, AVG(sales/quantity + discount) AS avg_price
-- 	FROM orders GROUP BY product_id
-- )

-- SELECT 
-- 	orders.product_id,
-- 	orders.discount,
-- 	orders.market,
-- 	orders.region,
-- 	orders.sales,
-- 	orders.quantity,
-- 	ROUND((orders.sales + orders.discount) / cte.avg_price) AS calculated_quantity
-- FROM 
-- 	orders
-- LEFT JOIN 
-- 	cte 
-- ON cte.product_id = orders.product_id
-- WHERE 
-- 	orders.quantity IS NULL or orders.quantity = 0
-- ORDER BY 
-- 	orders.product_id;

WITH missing AS (
	SELECT product_id,
		discount, 
		market,
		region,
		sales,
		quantity
	FROM orders 
	WHERE quantity IS NULL
), 

unit_prices AS (SELECT o.product_id,
	AVG(CAST(o.sales / o.quantity AS NUMERIC)) AS unit_price
FROM orders o
RIGHT JOIN missing AS m 
	ON o.product_id = m.product_id
	AND o.discount = m.discount
WHERE o.quantity IS NOT NULL
GROUP BY o.product_id
)

SELECT DISTINCT m.*,
	ROUND(CAST(m.sales AS NUMERIC) / up.unit_price,0) AS calculated_quantity
FROM missing AS m
INNER JOIN unit_prices AS up
	ON m.product_id = up.product_id;