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
/*
Find the top 5 products from each category based on highest total sales. The output should be sorted by category in ascending order and by sales in descending order within each category, i.e. within each category product with highest margin should sit on the top. Save the query as top_five_products_each_category, containing the following columns:
category
product_name
product_total_sales (rounded to two decimal places)
product_total_profit (rounded to two decimal places)
product_rank
*/
-- top_five_products_each_category
WITH
grouped_sales_by_category_and_product AS(
SELECT
p.category,
p.product_name,
SUM(o.sales) AS product_total_sales,
SUM(o.profit) AS product_total_profit,
DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(o.sales) DESC) AS product_rank
FROM products AS p
JOIN orders AS o
ON p.product_id = o.product_id
GROUP BY p.category, p.product_name
),
top_five_products_each_category AS(
SELECT
category,
product_name,
product_total_sales,
product_total_profit,
product_rank
FROM grouped_sales_by_category_and_product
WHERE product_rank <= 5
ORDER BY category ASC, product_total_sales DESC
)
SELECT *
FROM top_five_products_each_category
DataFrameas
impute_missing_values
variable
/*
Calculate the quantity for orders with missing values in the quantity column by determining the unit price for each product_id using available order data, considering relevant pricing factors such as discount, market, or region. Then, use this unit price to estimate the missing quantity values. The calculated values should be stored in the calculated_quantity column. Save query output as impute_missing_values, containing the following columns:
product_id
discount
market
region
sales
quantity
calculated_quantity (rounded to zero decimal places)
*/
-- impute_missing_values
WITH
calculated_unit_prices AS (
SELECT
product_id,
region,
market,
discount,
SUM(sales) / COUNT(*) AS calculated_unit_price
FROM orders
GROUP BY region, market, product_id, discount
),
impute_missing_values AS (
SELECT
o.product_id,
o.discount,
o.market,
o.region,
o.sales,
o.quantity,
CASE
WHEN o.quantity IS NULL THEN ROUND(CAST(o.sales / cup.calculated_unit_price AS numeric),0)
ELSE o.quantity
END AS calculated_quantity
FROM orders AS o
JOIN calculated_unit_prices AS cup
ON o.product_id = cup.product_id
AND o.market = cup.market
AND o.region = cup.region
AND o.discount = cup.discount
)
SELECT *
FROM impute_missing_values
WHERE quantity IS NULL