Skip to content

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:

ColumnDefinitionData typeComments
row_idUnique Record IDINTEGER
order_idIdentifier for each order in tableTEXTConnects to order_id in returned_orders table
order_dateDate when order was placedTEXT
marketMarket order_id belongs toTEXT
regionRegion Customer belongs toTEXTConnects to region in people table
product_idIdentifier of Product boughtTEXTConnects to product_id in products table
salesTotal Sales Amount for the Line ItemDOUBLE PRECISION
quantityTotal Quantity for the Line ItemDOUBLE PRECISION
discountDiscount applied for the Line ItemDOUBLE PRECISION
profitTotal Profit earned on the Line ItemDOUBLE PRECISION

returned_orders:

ColumnDefinitionData type
returnedYes values for Order / Line Item ReturnedTEXT
order_idIdentifier for each order in tableTEXT
marketMarket order_id belongs toTEXT

people:

ColumnDefinitionData type
personName of Salesperson credited with OrderTEXT
regionRegion Salesperson in operating inTEXT

products:

ColumnDefinitionData type
product_idUnique Identifier for the ProductTEXT
categoryCategory Product belongs toTEXT
sub_categorySub Category Product belongs toTEXT
product_nameDetailed Name of the ProductTEXT
  • 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!
-- impute_missing_values
WITH missing AS (SELECT quantity,
       product_id,
       discount,
       market,
       region,
       sales
FROM orders
WHERE quantity is NULL),
unit_prices AS (
SELECT o.product_id,
       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)
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;

SELECT order_id, order_date, COUNT(*) AS count
FROM orders
GROUP BY order_id, order_date
HAVING COUNT(*) > 1;

-- Yearly sales
SELECT 
    EXTRACT(YEAR FROM order_date::date) AS year,
	ROUND(CAST(SUM(sales) AS NUMERIC),2) AS total_sales
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date::date)
ORDER BY year ASC;
-- The sales show an increasing trend...good news!

-- Most profitable products - top 5
SELECT * FROM ( 
SELECT products.product_name, 
	   COUNT(quantity) as Qty_sold,
	  ROUND(SUM(CAST(ord.profit AS NUMERIC)),2) as product_total_profit
      FROM orders AS ord
      INNER JOIN products
              ON ord.product_id = products.product_id
      GROUP BY products.product_name
      ORDER BY product_total_profit DESC
	  LIMIT 5
    ) AS sub;

--Calculate Average transaction value and profit per order
SELECT EXTRACT(YEAR FROM order_date::date) AS year,
       SUM(profit) / SUM(quantity) as profit_per_order,
       SUM(sales) / SUM(quantity) as Average_Trnx_order
FROM orders
GROUP BY year
ORDER BY year ASC
-- AVG Tranx value is around 71$ and profit per order is around 8$, remains consistent every year!
Spinner
DataFrameas
top_five_products_each_category
variable
-- top_five_products_each_category
SELECT * FROM ( 
SELECT products.category,
	   products.product_name, 
	  ROUND(SUM(CAST(ord.sales AS NUMERIC)),2) as product_total_sales,
	  ROUND(SUM(CAST(ord.profit AS NUMERIC)),2) as product_total_profit,
      RANK() OVER(PARTITION BY products.category ORDER BY SUM(ord.sales) DESC) AS product_rank
      FROM orders AS ord
      INNER JOIN products
              ON ord.product_id = products.product_id
      GROUP BY products.category, products.product_name
	) AS tmp
      WHERE product_rank < 6;