DataBrighten

    Sales orders sample database for PostgreSQL

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

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

    textnumberdateyes/no

    Sales orders database for PostgreSQL

    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 PostgreSQL script: one table and 1,000 rows, with column types chosen for PostgreSQL, 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 PostgreSQL 15.18 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

    psql

    createdb practice
    psql -d practice -f sales-orders-postgresql.sql

    pgAdmin or DBeaver

    Create a database, open the Query Tool, open the .sql file and run it.

    Tables and columns

    sales_orders 1,000 rows

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

    Practice questions

    answers from PostgreSQL 15.18

    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;

    Multiplying by 1.0 avoids integer division.

    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 to_char(order_date, 'YYYY-MM') AS month, ROUND(SUM(sales), 2) AS sales
    FROM sales_orders
    WHERE EXTRACT(YEAR FROM order_date)::int = 2024
    GROUP BY to_char(order_date, 'YYYY-MM')
    ORDER BY month;

    to_char(date, 'YYYY-MM') formats a date as year-month text.

    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((ship_date - order_date)), 2) AS avg_days
    FROM sales_orders
    GROUP BY ship_mode
    ORDER BY avg_days, ship_mode;

    Subtracting one DATE from another returns the number of days as an integer.

    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;

    RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work on any supported version.

    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

    PostgreSQL differences used here

    • Subtracting one DATE from another returns the number of days as an integer.
    • Multiplying by 1.0 avoids integer division.
    • LIMIT n at the end returns the first n rows.
    • RANK() and ROW_NUMBER() OVER (PARTITION BY ...) work on any supported version.
    • to_char(date, 'YYYY-MM') formats a date as year-month text.