Chapter 29 · advanced · 6 min
Profiling a table you've never seen
This is the literal first task of most first days: someone hands you access to a table you've never opened and asks for a number out of it. Writing the analysis query before you understand the table is how wrong numbers get shipped confidently. Five checks, all just SELECT COUNT/MIN/MAX variations, done in order before anything else.
The dataset
A signups table with a mix of clean and messy rows — a duplicate, a null email, and a stray future date — planted on purpose so each check actually catches something.
Schema
| signup_id | INT |
| user_id | INT |
| TEXT | |
| signup_date | DATE |
Example data
Check 1: how many rows, really
The obvious first step, and worth stating exactly why: it's your baseline for every other check. If a later query returns more rows than this, something multiplied.
Before you run it — how many rows did the setup insert?
Check 2: is the key actually unique
Never assume a column named _id is a real primary key just because it looks like one — that's a schema-design decision someone made or didn't, not a guarantee. Compare a straight row count against a distinct-user count.
Before you run it — user_id 104 appears twice with identical data. Will the two counts match?
Check 3: null rate per column
A null-heavy column changes what any aggregate on it actually means. AVG, COUNT(col), and joins on that column all silently skip or mismatch nulls — you want to know the scale of that before it affects a result, not after.
Before you run it — one row has a NULL email. What does missing_email come out to?
Check 4: the date range makes sense
MIN/MAX on any date column, every time. A future-dated row is either a real scheduled record or a data-entry bug, and you cannot tell which without asking — but you can't ask if you never noticed the date was there.
Before you run it — one row is dated 2027-01-01, a year ahead of the rest. Will MAX(signup_date) surface it?
Check 5: find the exact duplicate rows
Check 2 told you a duplicate exists. This finds it, so you can decide what to do about it before it reaches a SUM or a COUNT further downstream — the fan-out lesson covers exactly what happens if you don't.
Before you run it — how many groups does this return, and what n do you expect for user_id 104?
If you've used Excel or Google Sheets
This is the same instinct as scrolling a new spreadsheet and eyeballing it before building a formula on top of it — checking COUNTA, sorting by a date column to spot outliers, using conditional formatting to spot duplicates. SQL just makes each check a precise, repeatable query instead of a visual scan, which matters once the table has more rows than you could ever scroll through.
Ready to practice? Shopee's order status breakdown and GXBank's average balance by account type are both "summarize a table you haven't seen before" in miniature.
Run these five before the real analysis, not after a number looks wrong — by the time it looks wrong it may already be in someone's report. Related: Joins that quietly inflate your numbers, Dates that quietly lose a day.