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

SELECT *
-- Create a subquery in the FROM statement that forms a table depicting the top 5 products per category based on highest total sales
FROM (
    SELECT
        pr.category,
        pr.product_name,
	-- The orders table has the sales field
        ROUND(SUM(o.sales)::numeric, 2) AS product_total_sales,
	-- The orders table has the profit field
        ROUND(SUM(o.profit)::numeric, 2) AS product_total_profit, 
-- Output should be sorted by category in ascending order and by sales in descending order within each category
        RANK() OVER(PARTITION BY pr.category ORDER BY ROUND(SUM(o.sales)::numeric, 2) DESC) AS product_rank
    FROM products AS pr
    INNER JOIN orders AS o 
	-- The unique identifier to join tables pr and o
    USING(product_id) 
    GROUP BY pr.category, pr.product_name
	 -- Aliasing the subquery as ranked_products
) AS ranked_products  
-- To find the top 5 per category
WHERE product_rank < 6;
Hidden output
Spinner
DataFrameas
impute_missing_values
variable
-- impute_missing_values

-- First CTE will generate all rows in the orders table with missing values for the quantity field
WITH missing AS (
	SELECT
		product_id,
		discount,
		market,
		region,
		sales, 
		quantity
	FROM orders
	WHERE quantity is NULL	
),
-- Second CTE will generate the unit prices for a single quantity of each product
unit_prices AS (
	SELECT
		orders.product_id,
		(orders.sales/orders.quantity)::numeric AS unit_price
	FROM orders
	RIGHT JOIN missing --
		ON orders.product_id = missing.product_id
		AND orders.discount = missing.discount
	WHERE orders.quantity IS NOT NULL
)
-- Estimate the missing quantity values
SELECT 
	DISTINCT missing.*,
	ROUND((missing.sales/unit_prices.unit_price)::numeric, 0) AS calculated_quantity
FROM missing
INNER JOIN unit_prices
	ON missing.product_id = unit_prices.product_id;
Hidden output