Sample orders dataset for SQL and spreadsheet exercises

Thirty fictional orders from nine countries in one small CSV file, with dates, channels, products, revenue and status. Ten tasks for spreadsheets and SQL with checked answers. Public domain, ready for SQL, spreadsheet and statistics lessons.

DatasetBachelor +2CC0 1.0

What you will learn

  • Describe a table by its columns and data types
  • Filter, group and total business data
  • Check a calculation by doing it two ways
  • Explain why refunded and pending orders must be filtered out of revenue figures

What is in it

One row per order, January to April 2026, eleven columns. All customers and companies are made up.

Column

Type

Example

Meaning

order_id

number

1001

Unique number of the order, 1001 to 1030

date

date

2026-01-04

Order date, written year-month-day

customer

text

Jordan Alvarez

The person who ordered

company

text

Acme Supply Co

The customer's company

country

text

Netherlands

The company's country

channel

text

Direct, Partner or Marketplace

How the order came in

product

text

Starter Plan, Pro Plan or Business Plan

What was ordered

units

number

2

How many were ordered

unit_price

number

49.00

Price of one unit: 49, 149 or 399

revenue

number

98.00

Value of the order

status

text

paid, refunded or pending

Whether the money arrived

revenue is always units times unit_price.

The file is comma-separated, with a header row, UTF-8 text and a full stop as the decimal sign. It has no empty cells. Dates are written year-month-day (ISO 8601), so they sort correctly even as text. Opened in a spreadsheet, the columns are A (order_id) to K (status), and the data sits in rows 2 to 31.

Watch out: refunded and pending orders still have a value in revenue. For "how much money came in", filter on status = 'paid' first.

Facts to check against

  • 30 orders: 25 paid, 3 refunded and 2 pending.

  • 12 customers from 9 companies in 9 countries.

  • Paid revenue by channel: Direct 4563, Marketplace 2474, Partner 2432.

  • The same table is built into the SQL Query Playground as its sample, so a class can start without downloading anything.

Ten tasks

Each task works in a spreadsheet and in SQL. In the playground the table is called data. Spreadsheet formulas below use semicolons between arguments; some language settings use commas instead.

  1. How many orders are there for each status?

  2. How much paid revenue came in altogether?

  3. Which country brought the most paid revenue? Which had the most paid orders?

  4. For each product: how many paid orders, units and how much paid revenue?

  5. How much paid revenue came in each month?

  6. What are the mean, the median, the smallest and the largest paid order?

  7. What is the average paid order value for each channel?

  8. What share of all orders was refunded, and in which channels?

  9. Which company brought the most paid revenue, and from how many customers?

  10. Check task 2 a second way: does the sum of the channel totals give the same figure?

Solutions

All answers were calculated from the file and checked in SQLite and in the playground.

1. Orders by status: 25 paid, 3 refunded, 2 pending.

Task 1
SELECT status, COUNT(*) AS orders FROM data GROUP BY status

Spreadsheet: =COUNTIF(K2:K31; "paid"), and the same for "refunded" and "pending", or a pivot table with status as rows.

2. Paid revenue: 9,469.00. (All 30 orders together: 10,955.00, which would overstate the money that came in.)

Task 2
SELECT ROUND(SUM(revenue), 2) AS paid_revenue FROM data WHERE status = 'paid'

Spreadsheet: =SUMIFS(J2:J31; K2:K31; "paid").

3. Countries: the United States, with 1,834.00 from 4 paid orders, then Germany 1,644.00, Japan 1,144.00 and Portugal 1,141.00. The Netherlands had the most paid orders (5) but only 1,091.00. Germany has the most orders overall (6), but two of them were refunded.

Task 3
SELECT country, ROUND(SUM(revenue), 2) AS revenue, COUNT(*) AS orders
FROM data
WHERE status = 'paid'
GROUP BY country
ORDER BY revenue DESC

4. Products (paid only):

Product

Orders

Units

Revenue

Pro Plan

12

27

4,023.00

Business Plan

6

8

3,192.00

Starter Plan

7

46

2,254.00

The Starter Plan sold the most units but brought the least revenue: it is the cheapest.

5. Months (paid only): January 1,587.00, February 2,081.00, March 3,228.00, April 2,573.00.

Task 5
SELECT SUBSTR(date, 1, 7) AS month, ROUND(SUM(revenue), 2) AS revenue
FROM data
WHERE status = 'paid'
GROUP BY SUBSTR(date, 1, 7)
ORDER BY month

Spreadsheet: add a helper column with =LEFT(B2; 7) and use it in a pivot table or SUMIFS.

6. Paid order values: mean 378.76, median 392.00, smallest 98.00, largest 798.00. There is no single mode: 149.00, 298.00 and 399.00 each appear four times.

Task 6
SELECT ROUND(AVG(revenue), 2) AS mean, MIN(revenue) AS smallest, MAX(revenue) AS largest
FROM data
WHERE status = 'paid'

The playground has no MEDIAN function, and standard SQLite builds do not have one either. Sort the 25 paid values and take the 13th: SELECT revenue FROM data WHERE status = 'paid' ORDER BY revenue LIMIT 1 OFFSET 12 returns 392.00.

7. Average paid order by channel: Marketplace 412.33 (6 orders), Partner 405.33 (6), Direct 351.00 (13). Direct brings the most money because it has the most orders, not because its orders are larger.

Task 7
SELECT channel, COUNT(*) AS orders, ROUND(AVG(revenue), 2) AS avg_order
FROM data
WHERE status = 'paid'
GROUP BY channel
ORDER BY avg_order DESC

8. Refunds: 3 of 30 orders, which is 10 percent. Partner 2 of 8 orders, Marketplace 1 of 7, Direct 0 of 15.

Task 8
SELECT channel, COUNT(*) AS refunds FROM data WHERE status = 'refunded' GROUP BY channel

Run it a second time without the WHERE line to get the number of orders per channel, then divide.

9. Companies: Vandelay Imports, 1,737.00 from two customers (one in Italy, one in Portugal), ahead of Northwind Trading with 1,336.00, also from two customers.

10. Check: 4,563 + 2,474 + 2,432 = 9,469. The channel totals add up to the figure from task 2, so neither query lost or doubled a row.

Ideas

  • SQL: the 45-minute exercise uses exactly this table.

  • Spreadsheets: a pivot table of revenue by country and product, then the same figures with SUMIFS. The spreadsheet functions exercise practises the formulas.

  • Statistics: the mean and median order value, and why they differ. The mean and median exercise explains both.

  • Charts: paid revenue per month as a column chart. What could explain the peak in March?

Common mistakes

  • Adding up revenue for all 30 rows. Three orders were refunded and two are not paid yet.

  • Grouping by customer when the question asks about companies: two companies have two customers each.

  • Expecting SUM(units) to be a measure of money. Units of different products have different prices.

License

The data is released into the public domain under CC0 1.0. Use it however you like, no credit needed.

Downloads

Reuse this material

CC0 1.0

It's in the public domain: use it however you like. A credit with a link is appreciated, not required.

"Sample orders dataset for SQL and spreadsheet exercises" by ManyPI (https://manypi.com/edu/sample-orders-dataset), licensed under CC0 1.0 (https://creativecommons.org/publicdomain/zero/1.0/)