DataBrighten

    Customers sample database for MySQL

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

    A ready-to-run MySQL script with a customer list with segments, order counts, lifetime spend and activity. Tested on MySQL 8.4.13. Updated 19 November 2026.

    textnumberdateyes/no

    Customers database for MySQL

    Each row is one fictional customer account: contact details on reserved example domains and 555-01xx numbers, city, signup date, segment, orders so far, lifetime spend and whether they ordered in 2024.

    This page has it as a MySQL script: one table and 1,000 rows, with column types chosen for MySQL, 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 customers dataset page, where it is also available as CSV and Excel.

    We loaded this exact file into MySQL 8.4.13 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

    mysql client

    mysql -u root -p -e "CREATE DATABASE practice"
    mysql -u root -p practice < customers-mysql.sql

    MySQL Workbench

    File > Open SQL Script, choose the database with USE practice; then Execute.

    Tables and columns

    customers 1,000 rows

    ColumnMySQL type
    customer_idVARCHAR(10)
    first_nameVARCHAR(20)
    last_nameVARCHAR(20)
    emailVARCHAR(40)
    phoneVARCHAR(20)
    cityVARCHAR(20)
    stateVARCHAR(10)
    signup_dateDATE
    segmentVARCHAR(20)
    total_ordersINT
    total_spentDECIMAL(10,2)
    last_order_dateDATE
    is_activeBOOLEAN

    Practice questions

    answers from MySQL 8.4.13

    How many customers are in each segment, and what share is active?

    SQL

    SELECT segment, COUNT(*) AS customers,
           ROUND(1.0 * 100 * SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS active_pct
    FROM customers
    GROUP BY segment
    ORDER BY customers DESC, segment;

    BOOLEAN is stored as TINYINT(1): TRUE is 1 and FALSE is 0.

    Result

    segmentcustomersactive_pct
    Consumer71845.40
    Small Business21149.76
    Enterprise7153.52
    Which 5 customers have spent the most?

    SQL

    SELECT customer_id, first_name, last_name, total_spent
    FROM customers
    ORDER BY total_spent DESC, customer_id
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    Result

    customer_idfirst_namelast_nametotal_spent
    CU10591WeiThompson24,199.09
    CU10976CharlesMartinez22,975.28
    CU10217CharlesTorres22,133.56
    CU10046DiegoJackson19,470.90
    CU10872DonnaMoore19,309.66
    How many customers signed up each year?

    SQL

    SELECT YEAR(signup_date) AS signup_year, COUNT(*) AS customers
    FROM customers
    GROUP BY YEAR(signup_date)
    ORDER BY signup_year;

    YEAR(date) returns the year as a number.

    Result

    signup_yearcustomers
    2019176
    2020152
    2021153
    2022166
    2023176
    2024177
    Which 5 states have the most customers?

    SQL

    SELECT state, COUNT(*) AS customers, ROUND(SUM(total_spent), 2) AS spent
    FROM customers
    GROUP BY state
    ORDER BY customers DESC, state
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    Result

    statecustomersspent
    CA151251,780.94
    TX123177,933.87
    NY102192,295.73
    IL62106,761.43
    FL5978,504.24
    How many customers have no last order date (never ordered)?

    SQL

    SELECT COUNT(*) AS no_order_date
    FROM customers
    WHERE last_order_date IS NULL;

    Result

    no_order_date
    53
    What is the average order value (total spent ÷ total orders) per segment?

    SQL

    SELECT segment, ROUND(1.0 * SUM(total_spent) / NULLIF(SUM(total_orders), 0), 2) AS avg_order_value
    FROM customers
    GROUP BY segment
    ORDER BY segment;

    1.0 * a / b gives a decimal result.

    Result

    segmentavg_order_value
    Consumer73.82
    Enterprise611.63
    Small Business208.85

    MySQL differences used here

    • BOOLEAN is stored as TINYINT(1): TRUE is 1 and FALSE is 0.
    • 1.0 * a / b gives a decimal result.
    • LIMIT n at the end returns the first n rows.
    • YEAR(date) returns the year as a number.