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
df3
variable
SELECT p.product_name, 
		SUM(o.sales), 
		RANK() OVER(ORDER BY SUM(o.sales) DESC) AS rank_no
	FROM products AS p
	LEFT JOIN orders AS o
	ON p.product_id = o.product_id
	GROUP BY p.product_name
	LIMIT 5
Spinner
DataFrameas
top_five_products_each_category
variable
-- top_five_products_each_category
SELECT * FROM (
	SELECT p.category,
			p.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,
			RANK() OVER(PARTITION BY p.category ORDER BY SUM(o.sales) DESC) AS product_rank
	FROM orders AS o
	INNER JOIN products AS p
	ON o.product_id = p.product_id
	GROUP BY p.category, p.product_name
) AS tmp
WHERE product_rank < 6;
Spinner
DataFrameas
df
variable
select * from orders
Spinner
DataFrameas
df2
variable
select *
from orders 
where quantity is null
Spinner
DataFrameas
df1
variable
select sales/quantity as unit_price
from orders
where quantity is not null
Spinner
DataFrameas
impute_missing_values
variable
-- impute_missing_values
WITH missing AS (
	SELECT product_id,
			discount,
			market,
			region,
			sales,
			quantity
	FROM orders
	WHERE quantity IS NULL
),
unit_prices AS (
	SELECT o.product_id,
			CAST(o.sales/o.quantity AS NUMERIC) AS unit_price
	FROM orders AS o
	RIGHT JOIN missing AS m
	ON o.product_id = m.product_id
	AND o.discount = m.discount
	WHERE o.quantity IS NOT NULL
)
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