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
df
variable
Spinner
DataFrameas
top_five_products_each_category
variable
-- top_five_products_each_category
WITH rankd as (SELECT category, 
		product_name, 
		ROUND(SUM(sales::numeric), 2)  AS product_total_sales, 
		ROUND(SUM(profit::numeric), 2) AS product_total_profit,
		RANK() OVER(PARTITION BY category ORDER BY SUM(sales::numeric) DESC) AS product_rank
FROM public.products AS p
LEFT JOIN orders AS o
ON o.product_id = p.product_id
GROUP BY product_name, category
ORDER BY category ASC, product_total_sales DESC)

SELECT * 
FROM rankd
WHERE product_rank < 6
Spinner
DataFrameas
impute_missing_values
variable
-- impute_missing_values

SELECT product_id,
		discount,
		market,
		region,
		sales,
		quantity, 
		CASE WHEN product_id = 'TEC-STA-10003330' THEN ROUND((sales/253.32)::numeric, 0) 
		 WHEN product_id = 'FUR-ADV-10000571' THEN ROUND((sales/109.74)::numeric, 0) 
		 WHEN product_id = 'FUR-BO-10001337' THEN ROUND((sales/102.833)::numeric, 0) 
		 WHEN product_id = 'TEC-STA-10004542' THEN ROUND((sales/40.08)::numeric, 0) 
		 WHEN product_id = 'FUR-ADV-10004395' THEN ROUND((sales/42.06)::numeric, 0) END AS calculated_quantity
		 
		
FROM public.orders
WHERE quantity IS NULL OR quantity <= 0