Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

Data profiling with MIN, MAX, COUNT DISTINCT, and AVG

Data profiling means running a quick snapshot of a column's extremes, average, and variety before touching anything - finding the lowest value, highest value, mean, and count of unique entries in one pass.

Example

Profiling Retail Product Data Before Cleaning

SELECT MIN(price) AS min_price,
       MAX(price) AS max_price,
       AVG(price) AS avg_price,
       COUNT(DISTINCT category) AS distinct_categories
FROM products

Result:

Step by step

1. Run the query without COUNT so you can see what the unfiltered values look like.

   SELECT MIN(price) AS min_price,
          MAX(price) AS max_price,
          AVG(price) AS avg_price,
          DISTINCT
   FROM products;

2. COUNT tells you how many rows match - perfect for sizing a table.

   SELECT MIN(price) AS min_price,
          MAX(price) AS max_price,
          AVG(price) AS avg_price,
          COUNT(DISTINCT category)
   FROM products;

3. AS gives the COUNT result a friendly column name so the output reads cleanly.

   SELECT MIN(price) AS min_price,
          MAX(price) AS max_price,
          AVG(price) AS avg_price,
          COUNT(DISTINCT category) AS distinct_categories
   FROM products;

Practice

Try each one in your head first, then open the answer.

SELECT AVG(revenue) FROM orders

SELECT AVG(price) FROM products

SELECT AVG(phone) FROM customers

SELECT AVG(revenue) FROM store_sales

SELECT AVG(refund_amount) FROM returns