DataBrighten

    Messy customers sample database for SQLite

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

    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.

    textnumberdateyes/no

    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

    ColumnSQLite type
    customer_idTEXT
    first_nameTEXT
    last_nameTEXT
    emailTEXT
    phoneTEXT
    cityTEXT
    stateTEXT
    signup_dateTEXT
    segmentTEXT
    total_ordersTEXT
    total_spentTEXT
    notesTEXT

    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_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, so 'A@x.com' and 'a@x.com' count as two values.

    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

    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.