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!
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!DataFrameavailable as
top_five_products_each_category
variable
-- top_five_products_each_category
WITH cte AS (
SELECT
products.category,
products.product_name,
SUM(orders.sales) AS product_total_sales,
SUM(orders.profit) AS product_total_profit,
RANK() OVER(PARTITION BY products.category ORDER BY SUM(orders.sales) DESC) AS product_rank
FROM
orders
JOIN
products
ON
products.product_id = orders.product_id
GROUP BY
category, product_name
)
SELECT
category,
product_name,
ROUND(product_total_profit::NUMERIC, 2) AS total_profit,
ROUND(product_total_sales::NUMERIC, 2) AS total_sales,
product_rank
FROM
cte
WHERE
product_rank <= 5
ORDER BY
category ASC, product_total_sales DESC;
DataFrameavailable as
salesperson_market_sales_details
variable
-- salesperson_market_sales_details
SELECT
people.person,
orders.market,
CASE WHEN orders.sales >= 0 and orders.sales < 100 THEN '0-100'
WHEN orders.sales >= 100 and orders.sales < 500 THEN '100-500'
ELSE '500+' END AS sales_bin,
COUNT(DISTINCT orders.order_id) AS order_counts,
SUM(CASE WHEN returned_orders.order_id IS NULL THEN 0 ELSE 1 END) AS orders_returned,
SUM(orders.sales) AS total_sales,
SUM(CASE WHEN returned_orders.order_id IS NULL THEN 0
ELSE orders.sales END) AS returned_sales
FROM
people
JOIN
orders
ON people.region = orders.region
LEFT JOIN
returned_orders
ON returned_orders.order_id = orders.order_id and returned_orders.market = orders.market
GROUP BY
people.person, orders.market, sales_bin
ORDER BY
people.person, orders.market, sales_bin;
DataFrameavailable as
impute_missing_values
variable
-- impute_missing_values
-- WITH cte AS (
-- SELECT product_id, AVG(sales/quantity + discount) AS avg_price
-- FROM orders GROUP BY product_id
-- )
-- SELECT
-- orders.product_id,
-- orders.discount,
-- orders.market,
-- orders.region,
-- orders.sales,
-- orders.quantity,
-- ROUND((orders.sales + orders.discount) / cte.avg_price) AS calculated_quantity
-- FROM
-- orders
-- LEFT JOIN
-- cte
-- ON cte.product_id = orders.product_id
-- WHERE
-- orders.quantity IS NULL or orders.quantity = 0
-- ORDER BY
-- orders.product_id;
WITH missing AS (
SELECT product_id,
discount,
market,
region,
sales,
quantity
FROM orders
WHERE quantity IS NULL
),
unit_prices AS (SELECT o.product_id,
AVG(CAST(o.sales / o.quantity AS NUMERIC)) AS unit_price
FROM orders o
RIGHT JOIN missing AS m
ON o.product_id = m.product_id
AND o.discount = m.discount
WHERE o.quantity IS NOT NULL
GROUP BY o.product_id
)
SELECT DISTINCT m.*,
ROUND(CAST(m.sales AS NUMERIC) / up.unit_price,0) AS calculated_quantity
FROM missing AS m
INNER JOIN unit_prices AS up
ON m.product_id = up.product_id;