Skip to content

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
DataFrameas
top_five_products_each_category
variable
--finding the top five products in each category by total product sales
WITH cte AS (
	SELECT
		p.category,
		p.product_name,
		ROUND(SUM(o.sales)::DECIMAL, 2) AS product_total_sales,
		ROUND(SUM(o.profit)::DECIMAL, 2) AS product_total_profit,
		RANK() OVER(PARTITION BY p.category ORDER BY SUM(o.sales)::DECIMAL DESC) AS product_rank
	FROM products AS p
	LEFT JOIN orders AS o
	ON p.product_id = o.product_id
	GROUP BY p.category, p.product_name
	ORDER BY category, product_rank)
	
SELECT
	category, 
	product_name,
	product_total_sales,
	product_total_profit,
	product_rank
FROM cte
WHERE product_rank <= 5;
Spinner
DataFrameas
impute_missing_values
variable
-- impute_missing_values: A query found there were five records that lacked a quantity, so this code finds the back-calculated quantity for each of these records based on other records for the same product id which did contain all data fields, including the quantity. Below are several DataFrames which work through some preliminary steps to get to the point of the final coding found here.
WITH cte AS (
	SELECT *
	FROM orders
	WHERE product_id IN (	
		SELECT product_id
		FROM orders
		WHERE quantity IS NULL)
	ORDER BY product_id),
	
	cte2 AS (
	SELECT
		product_id,
		AVG(sales/(quantity*(1-discount))) AS avg_unit
	FROM orders
	WHERE quantity IS NOT NULL
	GROUP BY product_id)
	
SELECT
	cte.product_id,
	cte.discount::DECIMAL,
	cte.market, 
	cte.region,
	ROUND(cte.sales::NUMERIC, 2) AS sales,
	cte.quantity::NUMERIC AS quantity,
	--calculated quantity
	(CASE WHEN cte.quantity IS NOT NULL THEN NULL
	 WHEN cte.quantity IS NULL THEN ROUND((cte.sales/(cte2.avg_unit*(1-cte.discount)))::NUMERIC, 0)
	 ELSE NULL END) AS calculated_quantity
	
FROM cte
LEFT JOIN cte2
ON cte.product_id = cte2.product_id
WHERE cte.quantity IS NULL
ORDER BY cte.product_id;

--unit price: (sales/(quantity*(1-discount)))::DECIMAL AS unit_price
Spinner
Queryas
all_id_equal_null_id
variable
--Working queries to be compiled in window above (#2 of the project)

--records where product_id matches the five product_id that are null
SELECT * 
FROM orders
WHERE product_id IN (	
	SELECT product_id
	FROM orders
	WHERE quantity IS NULL)
ORDER BY product_id;
Spinner
DataFrameas
unit_price_calc
variable
--Calculating the unit price
SELECT
	product_id,
	(sales/(quantity*(1-discount)))::DECIMAL AS unit_price,
	ROUND(sales::NUMERIC, 2) AS sales,
	quantity::NUMERIC AS quantity,
	discount::DECIMAL
FROM orders
LIMIT 15;
Spinner
DataFrameas
df
variable
--COMBINED (above): product_id matches the five product_id that are null + unit price

WITH cte AS (
	SELECT * 
	FROM orders
	WHERE product_id IN (	
		SELECT product_id
		FROM orders
		WHERE quantity IS NULL)
	ORDER BY product_id)
	
SELECT
	product_id,
	segment,
	market, 
	region,
	(sales/(quantity*(1-discount)))::DECIMAL AS unit_price,
	ROUND(sales::NUMERIC, 2) AS sales,
	quantity::NUMERIC AS quantity,
	discount::DECIMAL
FROM cte;
Spinner
DataFrameas
df1
variable
--We'll craft a query to match the product_id to the unit_price on the items that have quantity, then draw a WHERE equivalency from that to pull from for inserting into the calculated_quantity on the five rows that are missing the quantity
--Five rows, to represent the five product_id and unit_price on each:

WITH cte AS (
	SELECT * 
	FROM orders
	WHERE product_id IN (	
		SELECT product_id
		FROM orders
		WHERE quantity IS NULL)
	ORDER BY product_id)
	
SELECT
	product_id,
	AVG((sales/(quantity*(1-discount)))::DECIMAL) AS avg_unit_price
FROM cte
WHERE quantity IS NOT NULL
GROUP BY product_id;