DataBrighten

    Sales orders sample database for DuckDB

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

    A ready-to-run DuckDB script with superstore-style retail orders with sales, cost, discount and profit. Tested on DuckDB 1.5.6. Updated 19 November 2026.

    textnumberdateyes/no

    Sales orders database for DuckDB

    Each row is one order line from a fictional U.S. office-supply retailer: date, ship mode, customer, region, product, quantity, discount, sales and profit. Profit can be negative on heavily discounted lines, as in real retail data.

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

    DuckDB CLI

    duckdb practice.duckdb < sales-orders-duckdb.sql

    Tables and columns

    sales_orders 1,000 rows

    ColumnDuckDB type
    order_idVARCHAR
    order_dateDATE
    ship_dateDATE
    ship_modeVARCHAR
    customer_idVARCHAR
    customer_nameVARCHAR
    segmentVARCHAR
    regionVARCHAR
    stateVARCHAR
    cityVARCHAR
    categoryVARCHAR
    sub_categoryVARCHAR
    product_nameVARCHAR
    quantityINTEGER
    unit_priceDECIMAL(9,2)
    discountDECIMAL(6,2)
    salesDECIMAL(10,2)
    costDECIMAL(10,2)
    profitDECIMAL(9,2)

    Practice questions

    answers from DuckDB 1.5.6

    How many orders are there, and what are total sales and total profit?

    SQL

    SELECT COUNT(*) AS orders, ROUND(SUM(sales), 2) AS total_sales, ROUND(SUM(profit), 2) AS total_profit
    FROM sales_orders;

    Result

    orderstotal_salestotal_profit
    1000955,613.76214,946.97
    Which region has the highest sales? Show orders, sales and profit margin per region.

    SQL

    SELECT region, COUNT(*) AS orders, ROUND(SUM(sales), 2) AS sales,
           ROUND(1.0 * 100 * SUM(profit) / NULLIF(SUM(sales), 0), 2) AS margin_pct
    FROM sales_orders
    GROUP BY region
    ORDER BY sales DESC, region;

    1.0 * a / b gives a decimal result.

    Result

    regionorderssalesmargin_pct
    South281299,380.4122.50
    Midwest229234,801.8923.14
    West258217,262.6820.60
    Northeast232204,168.7823.75
    What were total sales in each month of 2024?

    SQL

    SELECT strftime(order_date, '%Y-%m') AS month, ROUND(SUM(sales), 2) AS sales
    FROM sales_orders
    WHERE year(order_date) = 2024
    GROUP BY strftime(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

    monthsales
    2024-0126,366.14
    2024-0229,322.17
    2024-0366,306.68
    2024-0437,334.21
    2024-0529,706.26
    2024-0617,953.87
    2024-0740,330.93
    2024-0837,906.14
    2024-0951,728.41
    2024-1026,125.41
    2024-1127,640.26
    2024-1238,613.94
    Which 5 products bring in the most profit?

    SQL

    SELECT product_name, ROUND(SUM(profit), 2) AS profit
    FROM sales_orders
    GROUP BY product_name
    ORDER BY profit DESC, product_name
    LIMIT 5;

    LIMIT n at the end returns the first n rows.

    Result

    product_nameprofit
    Desktop Copier34,340.80
    Workgroup Copier24,284.55
    Document Scanner18,015.23
    Cordless Phone Set14,305.57
    Desk Phone12,295.10
    How many days does each ship mode take on average, from order to shipping?

    SQL

    SELECT ship_mode, COUNT(*) AS orders, ROUND(AVG(date_diff('day', order_date, ship_date)), 2) AS avg_days
    FROM sales_orders
    GROUP BY ship_mode
    ORDER BY avg_days, ship_mode;

    date_diff('day', earlier, later) returns the number of days between two dates.

    Result

    ship_modeordersavg_days
    Same Day530.00
    First Class1441.52
    Second Class2132.95
    Standard5905.02
    Rank the categories by sales within each region (1 = highest).

    SQL

    SELECT region, category, ROUND(SUM(sales), 2) AS sales,
           RANK() OVER (PARTITION BY region ORDER BY SUM(sales) DESC) AS rnk
    FROM sales_orders
    GROUP BY region, category
    ORDER BY region, rnk, category;

    DuckDB supports all standard window functions.

    Result

    regioncategorysalesrnk
    MidwestTechnology162,922.131
    MidwestFurniture53,217.782
    MidwestOffice Supplies18,661.983
    NortheastTechnology113,871.901
    NortheastFurniture66,661.112
    NortheastOffice Supplies23,635.773
    SouthTechnology215,518.371
    SouthFurniture63,992.982
    SouthOffice Supplies19,869.063
    WestTechnology147,200.481
    WestFurniture51,849.612
    WestOffice Supplies18,212.593

    DuckDB differences used here

    • date_diff('day', earlier, later) returns the number of days between two dates.
    • 1.0 * a / b gives a decimal result.
    • LIMIT n at the end returns the first n rows.
    • DuckDB supports all standard window functions.
    • strftime(date, '%Y-%m') formats a date as year-month text (note the argument order differs from SQLite).