DataBrighten

    Messy customers sample database for DuckDB

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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.sql

    Tables and columns

    messy_customers 1,000 rows

    ColumnDuckDB type
    customer_idVARCHAR
    first_nameVARCHAR
    last_nameVARCHAR
    emailVARCHAR
    phoneVARCHAR
    cityVARCHAR
    stateVARCHAR
    signup_dateVARCHAR
    segmentVARCHAR
    total_ordersVARCHAR
    total_spentVARCHAR
    notesVARCHAR

    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_countdistinct_ids
    1000960
    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_distinctcleaned_distinct
    838827
    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

    staten
    New York13
    Texas12
    Florida8
    California7
    Illinois7
    Pennsylvania5
    Georgia4
    Indiana4
    Massachusetts4
    Nevada4
    Ohio4
    Oregon4
    Utah3
    Wisconsin3

    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.