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
-- top_five_products_each_category

WITH CTE AS (SELECT
	category,
	product_name,
	ROUND(SUM(CAST(o.sales AS numeric)), 2) AS product_total_sales,
	ROUND(SUM(CAST(o.profit AS numeric)), 2) AS product_total_profit,
	DENSE_RANK() OVER(PARTITION BY category ORDER BY ROUND(SUM(CAST(o.sales AS numeric)), 2) DESC) AS product_rank
FROM orders AS o  
LEFT JOIN products AS p
ON o.product_id = p.product_id
GROUP BY category, product_name
ORDER BY category ASC, product_total_sales DESC)

SELECT * FROM CTE
WHERE product_rank < 6;
Spinner
DataFrameas
impute_missing_values
variable
-- CTEs

WITH missing AS 
(SELECT
 	product_id, 
 	discount,
 	market,
 	region,
 	sales,
 	quantity
FROM orders 
WHERE quantity IS NULL),
unit_price AS
(SELECT
 	product_id,
	sales,
	quantity,
 	market,
	sales / quantity AS unit_price
FROM orders
WHERE quantity IS NOT NULL AND discount = 0)

SELECT
	DISTINCT m.*,
	ROUND(CAST(m.sales / up.unit_price AS numeric), 0) AS calculated_quantity
FROM missing AS m
LEFT JOIN unit_price AS up
ON m.product_id = up.product_id AND m.market = up.market;