Online store sample database for DuckDB
A ready-to-run DuckDB script with four related tables (customers, products, orders, order items) for JOIN practice. Tested on DuckDB 1.5.6. Updated 19 November 2026.
Online store database for DuckDB
Four related tables (customers, products, orders, order items) for JOIN practice.
This page has it as a DuckDB script: 4 related tables and 3,138 rows, with column types chosen for DuckDB, primary keys and foreign keys. The script drops the tables if they exist, creates them 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 online store 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("store-database-duckdb.sql").read())DuckDB CLI
duckdb practice.duckdb < store-database-duckdb.sqlTables and columns
customers 300 rows
| Column | DuckDB type |
|---|---|
| customer_id | VARCHAR |
| first_name | VARCHAR |
| last_name | VARCHAR |
| VARCHAR | |
| city | VARCHAR |
| state | VARCHAR |
| signup_date | DATE |
| segment | VARCHAR |
products 56 rows
| Column | DuckDB type |
|---|---|
| product_id | VARCHAR |
| category | VARCHAR |
| sub_category | VARCHAR |
| product_name | VARCHAR |
| unit_price | DECIMAL(9,2) |
| unit_cost | DECIMAL(9,2) |
orders 1,000 rows
| Column | DuckDB type |
|---|---|
| order_id | INTEGER |
| customer_id | VARCHAR |
| order_date | DATE |
| status | VARCHAR |
| ship_state | VARCHAR |
order_items 1,782 rows
| Column | DuckDB type |
|---|---|
| item_id | INTEGER |
| order_id | INTEGER |
| product_id | VARCHAR |
| quantity | INTEGER |
| unit_price | DECIMAL(9,2) |
Practice questions
answers from DuckDB 1.5.6
How many rows does each table have?
SQL
SELECT 'customers' AS tbl, COUNT(*) AS n FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
ORDER BY tbl;Result
| tbl | n |
|---|---|
| customers | 300 |
| order_items | 1782 |
| orders | 1000 |
| products | 56 |
What is revenue by product category, counting only delivered orders? (join 3 tables)
SQL
SELECT p.category, ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.status = 'Delivered'
GROUP BY p.category
ORDER BY revenue DESC, p.category;Result
| category | revenue |
|---|---|
| Technology | 464,694.57 |
| Furniture | 178,055.14 |
| Office Supplies | 58,016.22 |
Who are the 5 customers with the highest delivered revenue?
SQL
SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer,
ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY revenue DESC, c.customer_id
LIMIT 5;|| joins strings.
Result
| customer_id | customer | revenue |
|---|---|---|
| CU10118 | Steven Lewis | 21,983.66 |
| CU10237 | Elizabeth Flores | 14,375.98 |
| CU10292 | Carol Clark | 14,080.03 |
| CU10031 | Susan Carter | 13,974.42 |
| CU10039 | Mei Nelson | 13,634.07 |
How many customers have never placed an order? (LEFT JOIN)
SQL
SELECT COUNT(*) AS customers_without_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;Result
| customers_without_orders |
|---|
| 8 |
What was delivered revenue in each month?
SQL
SELECT strftime(o.order_date, '%Y-%m') AS month, COUNT(DISTINCT o.order_id) AS orders,
ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY strftime(o.order_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 | orders | revenue |
|---|---|---|
| 2024-01 | 59 | 49,376.78 |
| 2024-02 | 68 | 60,778.52 |
| 2024-03 | 69 | 62,303.52 |
| 2024-04 | 72 | 43,935.69 |
| 2024-05 | 75 | 66,275.68 |
| 2024-06 | 68 | 68,386.75 |
| 2024-07 | 58 | 49,342.41 |
| 2024-08 | 57 | 41,836.65 |
| 2024-09 | 69 | 45,975.24 |
| 2024-10 | 65 | 51,757.88 |
| 2024-11 | 68 | 70,541.49 |
| 2024-12 | 76 | 90,255.32 |
Which 5 products earn the highest gross margin (price minus cost) across all orders?
SQL
SELECT p.product_name, SUM(i.quantity) AS units,
ROUND(SUM(i.quantity * (i.unit_price - p.unit_cost)), 2) AS gross_margin
FROM order_items i
JOIN products p ON p.product_id = i.product_id
GROUP BY p.product_name
ORDER BY gross_margin DESC, p.product_name
LIMIT 5;LIMIT n at the end returns the first n rows.
Result
| product_name | units | gross_margin |
|---|---|---|
| Workgroup Copier | 65 | 38,620.50 |
| Desktop Copier | 82 | 34,134.62 |
| Cordless Phone Set | 58 | 19,166.43 |
| Label Printer | 79 | 18,494.60 |
| Desk Phone | 68 | 15,666.66 |
DuckDB differences used here
- || joins strings.
- 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).