Skip to content
Project: Analyzing and Formatting PostgreSQL Sales Data
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:
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. 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!
DataFrameas
top_five_products_each_category
variable
-- top_five_products_each_category
-- total sales per product and category
WITH total_sales AS (
SELECT p.category, p.product_name, ROUND(SUM(o.sales::numeric), 2) AS product_total_sales
FROM orders o
LEFT JOIN products p
ON o.product_id = p.product_id
GROUP BY p.category, p.product_name
),
-- profit per product and category
total_profits AS (
SELECT p.category, p.product_name, ROUND(SUM(o.profit::numeric), 2) AS product_total_profit
FROM orders o
LEFT JOIN products p
ON o.product_id = p.product_id
GROUP BY p.category, p.product_name
),
-- rank the product per category based on the highest total sales
rank_product AS (
SELECT p.category, p.product_name,
DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(o.sales) DESC) AS product_rank
FROM orders o
LEFT JOIN products p
ON o.product_id = p.product_id
GROUP BY p.category, p.product_name
)
SELECT ts.category, ts.product_name, ts.product_total_sales, tp.product_total_profit, rp.product_rank
FROM total_sales ts
LEFT JOIN total_profits tp
ON ts.category = tp.category
AND ts.product_name = tp.product_name
LEFT JOIN rank_product rp
ON ts.category = rp.category
AND ts.product_name = rp.product_name
WHERE rp.product_rank <= 5
ORDER BY ts.category ASC, ts.product_total_sales DESC;DataFrameas
impute_missing_values
variable
-- products with missing quantities
With missing_quantities AS (
SELECT product_id
FROM orders
WHERE quantity IS NULL
),
computation_unit_prices AS (
SELECT product_id,(
SUM(sales::numeric-(sales::numeric*discount::numeric)) / SUM(quantity::numeric)
) AS unit_price
FROM orders
WHERE quantity IS NOT NULL
GROUP BY product_id
)
-- final query that join the two queries
SELECT o.product_id, o.discount, o.market, o.region, o.sales, o.quantity, ROUND(SUM(sales::numeric / computation_unit_prices.unit_price), 0) AS calculated_quantity
FROM orders AS o
LEFT JOIN computation_unit_prices
ON o.product_id = computation_unit_prices.product_id
LEFT JOIN missing_quantities
ON computation_unit_prices.product_id = missing_quantities.product_id
WHERE o.quantity IS NULL
GROUP BY o.product_id, o.discount, o.market, o.region, o.sales, o.quantity
ORDER BY quantity DESC
DataFrameas
df
variable
SELECT product_id
FROM orders
WHERE quantity IS NULL