Messy customers sample database for SQLite
A ready-to-run SQLite script with a customer list with duplicates, blanks, mixed dates and text in number columns. Tested on SQLite 3.37.2. Updated 19 November 2026.
Messy customers database for SQLite
The customer table with problems added on purpose: repeated customer IDs, names in the wrong case, trailing spaces, blank emails, phone numbers in three formats, state names mixed with codes, dates in three formats, and $ signs or N/A in the spend column.
This page has it as a SQLite script: one table and 1,000 rows, with column types chosen for SQLite, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the messy customers dataset page, where it is also available as CSV and Excel.
We loaded this exact file into SQLite 3.37.2 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
Command line
sqlite3 practice.db < messy-customers-sqlite.sql
sqlite3 practice.db ".tables"Or skip the script
Download messy-customers.sqlite and open it in DB Browser for SQLite, DBeaver or: sqlite3 messy-customers.sqlite
Tables and columns
messy_customers 1,000 rows
| Column | SQLite type |
|---|---|
| customer_id | TEXT |
| first_name | TEXT |
| last_name | TEXT |
| TEXT | |
| phone | TEXT |
| city | TEXT |
| state | TEXT |
| signup_date | TEXT |
| segment | TEXT |
| total_orders | TEXT |
| total_spent | TEXT |
| notes | TEXT |
Practice questions
answers from SQLite 3.37.2
How many rows and how many distinct customer_id values are there? (duplicates show up as the difference)
SQL
SELECT COUNT(*) AS row_count, COUNT(DISTINCT customer_id) AS distinct_ids
FROM messy_customers;Result
| row_count | distinct_ids |
|---|---|
| 1000 | 960 |
How many rows have a missing or blank email?
SQL
SELECT COUNT(*) AS blank_emails
FROM messy_customers
WHERE (email IS NULL OR TRIM(email) = '');Result
| blank_emails |
|---|
| 44 |
After lower-casing and trimming, how many distinct emails remain, compared with the raw values?
SQL
SELECT COUNT(DISTINCT email) AS raw_distinct, COUNT(DISTINCT LOWER(TRIM(email))) AS cleaned_distinct
FROM messy_customers
WHERE NOT (email IS NULL OR TRIM(email) = '');Text comparison is case-sensitive by default, so 'A@x.com' and 'a@x.com' count as two values.
Result
| raw_distinct | cleaned_distinct |
|---|---|
| 838 | 827 |
Which state values are not a clean two-letter code? List them with counts.
SQL
SELECT state, COUNT(*) AS n
FROM messy_customers
WHERE LENGTH(state) <> 2
GROUP BY state
ORDER BY n DESC, state;LENGTH counts characters.
Result
| state | n |
|---|---|
| New York | 13 |
| Texas | 12 |
| Florida | 8 |
| California | 7 |
| Illinois | 7 |
| Pennsylvania | 5 |
| Georgia | 4 |
| Indiana | 4 |
| Massachusetts | 4 |
| Nevada | 4 |
| Ohio | 4 |
| Oregon | 4 |
| Utah | 3 |
| Wisconsin | 3 |
First 14 of 26 rows.
How many signup dates are not in ISO format (YYYY-MM-DD)?
SQL
SELECT COUNT(*) AS non_iso_dates
FROM messy_customers
WHERE signup_date NOT LIKE '____-__-__';Result
| non_iso_dates |
|---|
| 225 |
How many rows have a blank total_orders value?
SQL
SELECT COUNT(*) AS blank_total_orders
FROM messy_customers
WHERE (total_orders IS NULL OR TRIM(total_orders) = '');Result
| blank_total_orders |
|---|
| 69 |
SQLite differences used here
- Text comparison is case-sensitive by default, so 'A@x.com' and 'a@x.com' count as two values.
- LENGTH counts characters.