DataBrighten

    Online store sample database for DuckDB

    3,138rows · 4 tables · 6 questions with answers

    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.

    textnumberdateyes/no

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

    Tables and columns

    customers 300 rows

    ColumnDuckDB type
    customer_idVARCHAR
    first_nameVARCHAR
    last_nameVARCHAR
    emailVARCHAR
    cityVARCHAR
    stateVARCHAR
    signup_dateDATE
    segmentVARCHAR

    products 56 rows

    ColumnDuckDB type
    product_idVARCHAR
    categoryVARCHAR
    sub_categoryVARCHAR
    product_nameVARCHAR
    unit_priceDECIMAL(9,2)
    unit_costDECIMAL(9,2)

    orders 1,000 rows

    ColumnDuckDB type
    order_idINTEGER
    customer_idVARCHAR
    order_dateDATE
    statusVARCHAR
    ship_stateVARCHAR

    order_items 1,782 rows

    ColumnDuckDB type
    item_idINTEGER
    order_idINTEGER
    product_idVARCHAR
    quantityINTEGER
    unit_priceDECIMAL(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

    tbln
    customers300
    order_items1782
    orders1000
    products56
    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

    categoryrevenue
    Technology464,694.57
    Furniture178,055.14
    Office Supplies58,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_idcustomerrevenue
    CU10118Steven Lewis21,983.66
    CU10237Elizabeth Flores14,375.98
    CU10292Carol Clark14,080.03
    CU10031Susan Carter13,974.42
    CU10039Mei Nelson13,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

    monthordersrevenue
    2024-015949,376.78
    2024-026860,778.52
    2024-036962,303.52
    2024-047243,935.69
    2024-057566,275.68
    2024-066868,386.75
    2024-075849,342.41
    2024-085741,836.65
    2024-096945,975.24
    2024-106551,757.88
    2024-116870,541.49
    2024-127690,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_nameunitsgross_margin
    Workgroup Copier6538,620.50
    Desktop Copier8234,134.62
    Cordless Phone Set5819,166.43
    Label Printer7918,494.60
    Desk Phone6815,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).