Skip to content
Project: Analyzing and Formatting PostgreSQL Sales Data
Cleaning a PostgreSQL Database
In this project, data used is from a hypothetical Super Store to showcase SQL skills in data cleaning. This project will identify top categories based on the highest profit margins and detecting missing values, utilizing comprehensive knowledge of SQL concepts.
Data Dictionary:
orders:
orders:| Column | Definition | Data type | Comments |
|---|---|---|---|
row_id | Unique Record ID | INTEGER | |
order_id | Identifier for each order in table | TEXT | Connects to order_id in returned_orders table |
order_date | Date when order was placed | TEXT | |
market | Market order_id belongs to | TEXT | |
region | Region Customer belongs to | TEXT | Connects to region in people table |
product_id | Identifier of Product bought | TEXT | Connects to product_id in products table |
sales | Total Sales Amount for the Line Item | DOUBLE PRECISION | |
quantity | Total Quantity for the Line Item | DOUBLE PRECISION | |
discount | Discount applied for the Line Item | DOUBLE PRECISION | |
profit | Total Profit earned on the Line Item | DOUBLE PRECISION |
returned_orders:
returned_orders:| Column | Definition | Data type |
|---|---|---|
returned | Yes values for Order / Line Item Returned | TEXT |
order_id | Identifier for each order in table | TEXT |
market | Market order_id belongs to | TEXT |
people:
people:| Column | Definition | Data type |
|---|---|---|
person | Name of Salesperson credited with Order | TEXT |
region | Region Salesperson in operating in | TEXT |
products:
products:| Column | Definition | Data type |
|---|---|---|
product_id | Unique Identifier for the Product | TEXT |
category | Category Product belongs to | TEXT |
sub_category | Sub Category Product belongs to | TEXT |
product_name | Detailed Name of the Product | TEXT |
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. This will be corrected in some of the queries.
DataFrameas
top_five_products_each_category
variable
-- top_five_products_each_category
WITH top AS (
SELECT category, product_name,
SUM(sales) AS product_total_sales,
SUM(profit) AS product_total_profit,
RANK() OVER(PARTITION BY category ORDER BY SUM(sales) DESC) AS product_rank
FROM orders
INNER JOIN products
USING(product_id)
GROUP BY category, product_name)
SELECT category, product_name, product_total_sales, product_total_profit, product_rank
FROM top
WHERE product_rank <=5
ORDER BY category, product_total_sales DESC;
DataFrameas
impute_missing_values
variable
-- impute_missing_values
WITH missing AS(
SELECT product_id,
discount::NUMERIC,
market,
region,
sales::NUMERIC,
quantity::NUMERIC
FROM orders
WHERE quantity IS NULL),
base AS(
SELECT o.product_id,
o.sales::NUMERIC/o.quantity::NUMERIC AS base_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(m.sales/base_price,0) AS calculated_quantity
FROM missing AS m
INNER JOIN base AS b
USING(product_id);