Messy customers sample database for DuckDB
A ready-to-run DuckDB script with a customer list with duplicates, blanks, mixed dates and text in number columns. Tested on DuckDB 1.5.6. Updated 19 November 2026.
Messy customers database for DuckDB
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 DuckDB script: one table and 1,000 rows, with column types chosen for DuckDB, 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 DuckDB 1.5.6 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
Python
import duckdb
con = duckdb.connect("practice.duckdb")
con.execute(open("messy-customers-duckdb.sql").read())DuckDB CLI
duckdb practice.duckdb < messy-customers-duckdb.sqlTables and columns
messy_customers 1,000 rows
| Column | DuckDB type |
|---|---|
| customer_id | VARCHAR |
| first_name | VARCHAR |
| last_name | VARCHAR |
| VARCHAR | |
| phone | VARCHAR |
| city | VARCHAR |
| state | VARCHAR |
| signup_date | VARCHAR |
| segment | VARCHAR |
| total_orders | VARCHAR |
| total_spent | VARCHAR |
| notes | VARCHAR |
Practice questions
answers from DuckDB 1.5.6
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.
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 |
DuckDB differences used here
- Text comparison is case-sensitive by default.
- LENGTH counts characters.