Inventory sample database for DuckDB
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.
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.sqlTables and columns
inventory 1,000 rows
| Column | DuckDB type |
|---|---|
| sku | VARCHAR |
| product_name | VARCHAR |
| category | VARCHAR |
| warehouse | VARCHAR |
| quantity_on_hand | INTEGER |
| reorder_point | INTEGER |
| reorder_qty | INTEGER |
| unit_cost | DECIMAL(9,2) |
| unit_price | DECIMAL(9,2) |
| stock_value | DECIMAL(11,2) |
| supplier | VARCHAR |
| last_restock_date | DATE |
| needs_reorder | BOOLEAN |
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
| warehouse | skus | units | stock_value |
|---|---|---|---|
| WH1 Newark | 280 | 26911 | 4,470,329.76 |
| WH2 Chicago | 272 | 26014 | 3,685,288.21 |
| WH4 Reno | 224 | 19666 | 3,350,580.77 |
| WH3 Dallas | 224 | 21832 | 3,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
| warehouse | needs_reorder |
|---|---|
| WH4 Reno | 54 |
| WH1 Newark | 52 |
| WH2 Chicago | 48 |
| WH3 Dallas | 46 |
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
| sku | product_name | unit_margin |
|---|---|---|
| P0056-WH1 | Workgroup Copier | 610.05 |
| P0056-WH1-1 | Workgroup Copier | 610.05 |
| P0056-WH1-2 | Workgroup Copier | 610.05 |
| P0056-WH1-3 | Workgroup Copier | 610.05 |
| P0056-WH1-4 | Workgroup Copier | 610.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
| category | avg_markup_pct |
|---|---|
| Office Supplies | 58.63 |
| Technology | 53.12 |
| Furniture | 48.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
| month | restocks |
|---|---|
| 2024-01 | 75 |
| 2024-02 | 76 |
| 2024-03 | 89 |
| 2024-04 | 90 |
| 2024-05 | 88 |
| 2024-06 | 74 |
| 2024-07 | 76 |
| 2024-08 | 88 |
| 2024-09 | 70 |
| 2024-10 | 101 |
| 2024-11 | 86 |
| 2024-12 | 87 |
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).