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!
-- top_five_products_each_category
WITH product_totals AS(
SELECT p.category,
p.product_name,
SUM(o.sales) AS product_total_sales_raw,
SUM(o.profit) AS product_total_profit_raw
FROM public.orders o
JOIN public.products p
ON o.product_id = p.product_id
GROUP BY p.category, p.product_name
), ranked AS (
SELECT
category,
product_name,
ROUND(CAST(product_total_sales_raw AS NUMERIC), 2) AS product_total_sales,
ROUND(CAST(product_total_profit_raw AS NUMERIC), 2) AS product_total_profit,
RANK () OVER (
PARTITION BY category ORDER BY product_total_sales_raw DESC
) AS product_rank
FROM product_totals
)
SELECT
category,
product_name,
product_total_sales,
product_total_profit,
product_rank
FROM ranked
WHERE product_rank <= 5
ORDER BY category ASC, product_total_sales DESC, product_name ASC;WITH
-- 1) Rows where quantity is missing
missing AS (
SELECT
o.product_id,
o.discount,
o.market,
o.region,
o.sales,
o.quantity
FROM public.orders o
WHERE o.quantity IS NULL
),
-- 2) Unit price per product_id + discount from available data
unit_prices AS (
SELECT
o.product_id,
o.discount,
CAST(SUM(o.sales) / NULLIF(SUM(o.quantity), 0) AS NUMERIC) AS unit_price
FROM public.orders o
WHERE o.quantity IS NOT NULL AND o.quantity > 0
GROUP BY o.product_id, o.discount
)
-- Final: compute calculated_quantity for missing rows
SELECT DISTINCT
m.product_id,
m.discount,
m.market,
m.region,
m.sales,
m.quantity,
ROUND( CAST(m.sales AS NUMERIC) / up.unit_price, 0 ) AS calculated_quantity
FROM missing m
INNER JOIN unit_prices up
ON up.product_id = m.product_id
AND up.discount IS NOT DISTINCT FROM m.discount;