Invoices sample database for DuckDB
A ready-to-run DuckDB script with invoices with due dates, payment dates, tax, status and days late. Tested on DuckDB 1.5.6. Updated 19 November 2026.
Invoices database for DuckDB
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 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 invoices 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("invoices-duckdb.sql").read())DuckDB CLI
duckdb practice.duckdb < invoices-duckdb.sqlTables and columns
invoices 1,000 rows
| Column | DuckDB type |
|---|---|
| invoice_id | VARCHAR |
| client | VARCHAR |
| issue_date | DATE |
| due_date | DATE |
| paid_date | DATE |
| subtotal | DECIMAL(9,2) |
| tax_rate | DECIMAL(8,4) |
| tax | DECIMAL(8,2) |
| total | DECIMAL(10,2) |
| status | VARCHAR |
| days_late | INTEGER |
Practice questions
answers from DuckDB 1.5.6
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(date_diff('day', due_date, paid_date)), 2) AS avg_days_late
FROM invoices
WHERE paid_date > due_date;date_diff('day', earlier, later) returns 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(issue_date, '%Y-%m') AS month, ROUND(SUM(total), 2) AS invoiced
FROM invoices
GROUP BY strftime(issue_date, '%Y-%m')
ORDER BY month;strftime(date, '%Y-%m') formats a date as year-month text (note the argument order differs from SQLite).
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 |
DuckDB differences used here
- date_diff('day', earlier, later) returns the number of days between two dates.
- LIMIT n at the end returns the first n rows.
- strftime(date, '%Y-%m') formats a date as year-month text (note the argument order differs from SQLite).