Customers sample database for SQLite
A ready-to-run SQLite script with a customer list with segments, order counts, lifetime spend and activity. Tested on SQLite 3.37.2. Updated 19 November 2026.
Customers database for SQLite
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 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 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 < customers-sqlite.sql
sqlite3 practice.db ".tables"Or skip the script
Download customers.sqlite and open it in DB Browser for SQLite, DBeaver or: sqlite3 customers.sqlite
Tables and columns
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 | INTEGER |
| total_spent | REAL |
| last_order_date | TEXT |
| is_active | INTEGER |
Practice questions
answers from SQLite 3.37.2
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;SQLite has no boolean type: yes/no columns hold 1 and 0.
Result
| segment | customers | active_pct |
|---|---|---|
| Consumer | 718 | 45.40 |
| Small Business | 211 | 49.76 |
| Enterprise | 71 | 53.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_id | first_name | last_name | total_spent |
|---|---|---|---|
| CU10591 | Wei | Thompson | 24,199.09 |
| CU10976 | Charles | Martinez | 22,975.28 |
| CU10217 | Charles | Torres | 22,133.56 |
| CU10046 | Diego | Jackson | 19,470.90 |
| CU10872 | Donna | Moore | 19,309.66 |
How many customers signed up each year?
SQL
SELECT CAST(strftime('%Y', signup_date) AS INTEGER) AS signup_year, COUNT(*) AS customers
FROM customers
GROUP BY CAST(strftime('%Y', signup_date) AS INTEGER)
ORDER BY signup_year;strftime('%Y', date) returns text, so it is cast to INTEGER.
Result
| signup_year | customers |
|---|---|
| 2019 | 176 |
| 2020 | 152 |
| 2021 | 153 |
| 2022 | 166 |
| 2023 | 176 |
| 2024 | 177 |
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
| state | customers | spent |
|---|---|---|
| CA | 151 | 251,780.94 |
| TX | 123 | 177,933.87 |
| NY | 102 | 192,295.73 |
| IL | 62 | 106,761.43 |
| FL | 59 | 78,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;Multiplying by 1.0 forces decimal division instead of integer division.
Result
| segment | avg_order_value |
|---|---|
| Consumer | 73.82 |
| Enterprise | 611.63 |
| Small Business | 208.85 |
SQLite differences used here
- SQLite has no boolean type: yes/no columns hold 1 and 0.
- Multiplying by 1.0 forces decimal division instead of integer division.
- LIMIT n at the end returns the first n rows.
- strftime('%Y', date) returns text, so it is cast to INTEGER.