DataBrighten

    Invoices sample database for DuckDB

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

    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.

    textnumberdateyes/no

    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.sql

    Tables and columns

    invoices 1,000 rows

    ColumnDuckDB type
    invoice_idVARCHAR
    clientVARCHAR
    issue_dateDATE
    due_dateDATE
    paid_dateDATE
    subtotalDECIMAL(9,2)
    tax_rateDECIMAL(8,4)
    taxDECIMAL(8,2)
    totalDECIMAL(10,2)
    statusVARCHAR
    days_lateINTEGER

    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

    statusinvoicestotal
    Paid6392,429,763.23
    Paid late215799,346.67
    Overdue146506,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

    unpaidoutstanding
    146506,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_lateavg_days_late
    21522.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

    clientinvoicesinvoiced
    Granite Dental Group71289,367.35
    Maple Design Studio63286,534.26
    Cedar Design Studio66246,002.47
    Delta Logistics LLC59243,071.27
    Redwood Print Shop58237,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

    monthinvoiced
    2024-01297,105.81
    2024-02298,363.89
    2024-03292,532.10
    2024-04288,950.21
    2024-05309,437.91
    2024-06366,142.27
    2024-07313,354.37
    2024-08414,329.34
    2024-09304,277.59
    2024-10291,716.19
    2024-11309,157.08
    2024-12250,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).