Invoices sample database for SQLite
A ready-to-run SQLite script with invoices with due dates, payment dates, tax, status and days late. Tested on SQLite 3.37.2. Updated 19 November 2026.
Invoices database for SQLite
Each row is one invoice issued in 2024 to a fictional client: issue and due dates, paid date, subtotal, tax rate and amount, total, status as of 31 January 2025 and days late.
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 invoices 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 < invoices-sqlite.sql
sqlite3 practice.db ".tables"Or skip the script
Download invoices.sqlite and open it in DB Browser for SQLite, DBeaver or: sqlite3 invoices.sqlite
Tables and columns
invoices 1,000 rows
| Column | SQLite type |
|---|---|
| invoice_id | TEXT |
| client | TEXT |
| issue_date | TEXT |
| due_date | TEXT |
| paid_date | TEXT |
| subtotal | REAL |
| tax_rate | REAL |
| tax | REAL |
| total | REAL |
| status | TEXT |
| days_late | INTEGER |
Practice questions
answers from SQLite 3.37.2
How many invoices are in each status, and what is their total value?
SQL
SELECT status, COUNT(*) AS invoices, ROUND(SUM(total), 2) AS total
FROM invoices
GROUP BY status
ORDER BY invoices DESC, status;Result
| status | invoices | total |
|---|---|---|
| Paid | 639 | 2,429,763.23 |
| Paid late | 215 | 799,346.67 |
| Overdue | 146 | 506,974.73 |
How many invoices have no paid date yet, and how much is outstanding?
SQL
SELECT COUNT(*) AS unpaid, ROUND(SUM(total), 2) AS outstanding
FROM invoices
WHERE paid_date IS NULL;Result
| unpaid | outstanding |
|---|---|
| 146 | 506,974.73 |
On average, how many days late are invoices that were paid after the due date? (computed from the dates)
SQL
SELECT COUNT(*) AS paid_late, ROUND(AVG(CAST(julianday(paid_date) - julianday(due_date) AS INTEGER)), 2) AS avg_days_late
FROM invoices
WHERE paid_date > due_date;julianday(b) - julianday(a) gives the number of days between two dates.
Result
| paid_late | avg_days_late |
|---|---|
| 215 | 22.50 |
Which 5 clients were invoiced the most?
SQL
SELECT client, COUNT(*) AS invoices, ROUND(SUM(total), 2) AS invoiced
FROM invoices
GROUP BY client
ORDER BY invoiced DESC, client
LIMIT 5;LIMIT n at the end returns the first n rows.
Result
| client | invoices | invoiced |
|---|---|---|
| Granite Dental Group | 71 | 289,367.35 |
| Maple Design Studio | 63 | 286,534.26 |
| Cedar Design Studio | 66 | 246,002.47 |
| Delta Logistics LLC | 59 | 243,071.27 |
| Redwood Print Shop | 58 | 237,442.06 |
What was the total invoiced per month of issue?
SQL
SELECT strftime('%Y-%m', issue_date) AS month, ROUND(SUM(total), 2) AS invoiced
FROM invoices
GROUP BY strftime('%Y-%m', issue_date)
ORDER BY month;strftime('%Y-%m', date) turns a date into year-month text.
Result
| month | invoiced |
|---|---|
| 2024-01 | 297,105.81 |
| 2024-02 | 298,363.89 |
| 2024-03 | 292,532.10 |
| 2024-04 | 288,950.21 |
| 2024-05 | 309,437.91 |
| 2024-06 | 366,142.27 |
| 2024-07 | 313,354.37 |
| 2024-08 | 414,329.34 |
| 2024-09 | 304,277.59 |
| 2024-10 | 291,716.19 |
| 2024-11 | 309,157.08 |
| 2024-12 | 250,717.87 |
Check the data: how many rows have a total that differs from subtotal + tax by more than one cent?
SQL
SELECT COUNT(*) AS mismatches
FROM invoices
WHERE ABS(total - (subtotal + tax)) > 0.01;Result
| mismatches |
|---|
| 0 |
SQLite differences used here
- julianday(b) - julianday(a) gives the number of days between two dates.
- LIMIT n at the end returns the first n rows.
- strftime('%Y-%m', date) turns a date into year-month text.