Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

Range checks and flagging values outside expected bounds

A range check flags numbers that fall outside a set of realistic boundaries - too low, too high, or outright impossible.

Example

Flagging Retail Values Outside Expected Ranges

SELECT order_id, price, discount, quantity,
  CASE
    WHEN price <= 0 THEN 'Invalid price'
    WHEN discount NOT BETWEEN 0 AND 1 THEN 'Invalid discount'
    WHEN quantity < 1 THEN 'Invalid quantity'
    ELSE 'OK'
  END AS data_flag
FROM orders
WHERE price <= 0 OR discount NOT BETWEEN 0 AND 1 OR quantity < 1

Result:

Step by step

1. AS gives a column a friendlier name in the result.

   SELECT order_id, price, discount, quantity,
     CASE
       WHEN price <= 0 THEN 'Invalid price'
       WHEN discount NOT BETWEEN 0 AND 1 THEN 'Invalid discount'
       WHEN quantity < 1 THEN 'Invalid quantity'
       ELSE 'OK'
     END AS data_flag

2. FROM tells SQL which table to pull the data from.

   SELECT order_id, price, discount, quantity,
     CASE
       WHEN price <= 0 THEN 'Invalid price'
       WHEN discount NOT BETWEEN 0 AND 1 THEN 'Invalid discount'
       WHEN quantity < 1 THEN 'Invalid quantity'
       ELSE 'OK'
     END AS data_flag
   FROM orders

3. WHERE keeps only the rows that match your condition.

   SELECT order_id, price, discount, quantity,
     CASE
       WHEN price <= 0 THEN 'Invalid price'
       WHEN discount NOT BETWEEN 0 AND 1 THEN 'Invalid discount'
       WHEN quantity < 1 THEN 'Invalid quantity'
       ELSE 'OK'
     END AS data_flag
   FROM orders
   WHERE price <= 0 OR discount NOT BETWEEN 0 AND 1 OR quantity < 1;

Practice

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

SELECT * FROM orders WHERE revenue < 0 OR revenue > 100

SELECT * FROM store_sales WHERE revenue < 0 OR revenue > 100

SELECT * FROM products WHERE price < 0 OR price > 100

SELECT * FROM employees WHERE salary < 0 OR salary > 100

SELECT * FROM reviews WHERE rating < 1 OR rating > 5