DataBrighten

    Inventory sample database for DuckDB

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

    A ready-to-run DuckDB script with stock levels, reorder points and stock value across four warehouses. Tested on DuckDB 1.5.6. Updated 19 November 2026.

    textnumberdateyes/no

    Inventory database for DuckDB

    Each row is one SKU in one warehouse: quantity on hand, reorder point and quantity, unit cost and price, stock value, supplier code, last restock date and a needs_reorder flag you can check with a formula.

    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 inventory 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("inventory-duckdb.sql").read())

    DuckDB CLI

    duckdb practice.duckdb < inventory-duckdb.sql

    Tables and columns

    inventory 1,000 rows

    ColumnDuckDB type
    skuVARCHAR
    product_nameVARCHAR
    categoryVARCHAR
    warehouseVARCHAR
    quantity_on_handINTEGER
    reorder_pointINTEGER
    reorder_qtyINTEGER
    unit_costDECIMAL(9,2)
    unit_priceDECIMAL(9,2)
    stock_valueDECIMAL(11,2)
    supplierVARCHAR
    last_restock_dateDATE
    needs_reorderBOOLEAN

    Practice questions

    answers from DuckDB 1.5.6

    What is the stock value held in each warehouse?

    SQL

    SELECT warehouse, COUNT(*) AS skus, SUM(quantity_on_hand) AS units, ROUND(SUM(stock_value), 2) AS stock_value
    FROM inventory
    GROUP BY warehouse
    ORDER BY stock_value DESC, warehouse;

    Result

    warehouseskusunitsstock_value
    WH1 Newark280269114,470,329.76
    WH2 Chicago272260143,685,288.21
    WH4 Reno224196663,350,580.77
    WH3 Dallas224218323,200,945.35
    How many SKUs need reordering in each warehouse?

    SQL

    SELECT warehouse, SUM(CASE WHEN needs_reorder THEN 1 ELSE 0 END) AS needs_reorder
    FROM inventory
    GROUP BY warehouse
    ORDER BY needs_reorder DESC, warehouse;

    Yes/no columns are real BOOLEAN values.

    Result

    warehouseneeds_reorder
    WH4 Reno54
    WH1 Newark52
    WH2 Chicago48
    WH3 Dallas46
    Does needs_reorder agree with quantity_on_hand <= reorder_point? Count the rows where it does not.

    SQL

    SELECT COUNT(*) AS mismatches
    FROM inventory
    WHERE (quantity_on_hand <= reorder_point) <> (needs_reorder);

    Yes/no columns are real BOOLEAN values.

    Result

    mismatches
    0
    Which 5 products have the highest margin per unit?

    SQL

    SELECT sku, product_name, ROUND(unit_price - unit_cost, 2) AS unit_margin
    FROM inventory
    ORDER BY unit_margin DESC, sku
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    Result

    skuproduct_nameunit_margin
    P0056-WH1Workgroup Copier610.05
    P0056-WH1-1Workgroup Copier610.05
    P0056-WH1-2Workgroup Copier610.05
    P0056-WH1-3Workgroup Copier610.05
    P0056-WH1-4Workgroup Copier610.05
    What is the average markup percentage by category?

    SQL

    SELECT category, ROUND(AVG(100 * (unit_price - unit_cost) / unit_cost), 2) AS avg_markup_pct
    FROM inventory
    GROUP BY category
    ORDER BY avg_markup_pct DESC, category;

    AVG works on integers and decimals directly.

    Result

    categoryavg_markup_pct
    Office Supplies58.63
    Technology53.12
    Furniture48.89
    How many restocks happened in each month?

    SQL

    SELECT strftime(last_restock_date, '%Y-%m') AS month, COUNT(*) AS restocks
    FROM inventory
    GROUP BY strftime(last_restock_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

    monthrestocks
    2024-0175
    2024-0276
    2024-0389
    2024-0490
    2024-0588
    2024-0674
    2024-0776
    2024-0888
    2024-0970
    2024-10101
    2024-1186
    2024-1287

    DuckDB differences used here

    • AVG works on integers and decimals directly.
    • Yes/no columns are real BOOLEAN values.
    • 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).