Core SQL Queries for Data Cleaning and Analysis#

🧽 Data Cleaning & Preparation 🐬 Cleaning with SQL Lesson 020

Important

✨ AI-generated content. This page was written with the assistance of an AI language model and is provided as a learning aid. Despite careful review, it may still contain mistakes, omissions, or out-of-date information. Whether you are new to the topic, a team lead, or a senior practitioner, treat it as a starting point rather than an authoritative reference: read it critically and independently verify anything you act on (code, commands, figures, and factual claims) against official documentation and primary sources before relying on it.

The query patterns that clean#

SQL cleaning rests on a small set of core query patterns — combinations of clauses that inspect, filter, and summarise data. These patterns do the bulk of practical cleaning and analysis, and they extend the basic SELECT/FROM/ WHERE from the prep section with the tools that make queries genuinely powerful. This lesson assembles the working toolkit.

Inspecting and filtering#

The starting patterns find and isolate what needs attention:

SELECT DISTINCT region          -- what distinct values exist? (consistency check)
FROM   customers;

SELECT *                        -- which rows have a problem?
FROM   customers
WHERE  spend < 0;               -- invalid negative values

SELECT *
FROM   customers
WHERE  city IS NULL;            -- missing values (note: IS NULL, not = NULL)

SELECT DISTINCT lists the unique values in a column — the SQL version of the consistency-audit pivot, revealing whether “NY”, “New York”, and “new york” all lurk in the data. WHERE isolates rows failing a validity rule. Note IS NULL (not = NULL) is how SQL tests for missing values — a common beginner trap, since = NULL never matches.

Summarising with GROUP BY#

The pattern that turns rows into insight is GROUP BY, which collapses rows into groups and computes an aggregate per group:

SELECT   region,
         COUNT(*)     AS n,
         AVG(spend)   AS avg_spend
FROM     customers
GROUP BY region
ORDER BY n DESC;

This counts customers and averages spend per region — the SQL equivalent of a pivot table, and one of the most-used analytical patterns. Grouping by a column and counting is also a cleaning tool: GROUP BY email HAVING COUNT(*) > 1 finds duplicate emails (HAVING filters groups, as WHERE filters rows).

The aggregate functions#

GROUP BY pairs with aggregate functions — the same operations as the spreadsheet’s: COUNT (how many), SUM (total), AVG (mean), MIN / MAX (extremes). These collapse many rows to one summary value, per group or over the whole table. Recognising them as the familiar spreadsheet aggregates, now in query form, is what makes SQL analysis feel like an extension of what you already know rather than a new discipline.

Putting the patterns together#

Real cleaning and analysis chains these: filter to valid rows with WHERE, group with GROUP BY, aggregate with COUNT/AVG, order with ORDER BY, and inspect distinct values with DISTINCT to check consistency along the way. A handful of clauses, combined, answer a large share of real questions — which is why SQL stays learnable despite its power, and why these core patterns are the foundation the analysis section’s advanced queries build on.

The caveat#

The core patterns are precise about what they compute, which is not always what you intend: COUNT(*) counts rows including nulls while COUNT(column) skips nulls, AVG silently ignores nulls (changing the denominator), and a GROUP BY on an uncleaned column groups “NY” and “New York” separately — producing a summary that looks authoritative but is fragmented by the very dirtiness you were checking for. The lesson underneath: clean before you aggregate, because summarising dirty data produces confident, wrong totals. The next lessons apply these patterns to specific cleaning tasks — removing duplicates, cleaning strings, and converting types.

Hint