A worked example

Build a sales report,
and check your totals.

Five files that join on documented keys, four questions a sales report answers, and the numbers you should get. Do it first on the free samples, then on the complete data.

The samples are free and need no account. The store, its customers and its orders are made up.

What you will getFictional business

Net sales by year

Delivered orders, after discounts, without shipping.

Net sales by year from the complete data
YearComplete data
2022$3,074,025.23
2023$3,641,394.46
2024$4,615,040.73
2025$5,350,876.70

If your query returns these four numbers, your joins and filters are right.

Step 1

Get the five files.

They come from three collections. The free samples are chosen to fit together: the sample orders are the ones placed by the people in the People sample, with every line and product on those orders, and the calendar sample covers the years of the orders.

FileOne row perCollectionFree sample
StoreOrdersOrderSample storeDownload the three files (CSV)
StoreOrderItemsProduct on an order
StoreProductsProduct and color
PeopleCustomerPeopleDownload (CSV)
CalendarDatesDayCalendar tableDownload (CSV)

Load each file into a table of the same name in your database, or into the data model of Excel or Power BI. JSON and XML are on each collection’s page.

Step 2

Join them on four keys.

Each key on the left holds a value from the column on the right. In Excel or Power BI these are the four relationships to create.

This columnMatchesMeaning
StoreOrderItems.OrderIDStoreOrders.IDEach line belongs to one order.
StoreOrderItems.ProductIDStoreProducts.IDEach line is one product.
StoreOrders.PersonIDPeople.IDEach order was placed by one person.
StoreOrders.OrderDateCalendarDates.CalendarDateEach order has one row of calendar facts for its date.

Check the joins before the sums. Count the rows of StoreOrderItems, then count them again after joining to orders and products. The two counts are the same in the samples and in the complete data, because no line is missing its order or its product.

Step 3

Agree on the rules of the report.

Two reports of the same sales disagree when they treat returns differently. These are the rules behind every number on this page.

  • Net sales is Subtotal - DiscountAmount on the order. It equals the sum of that order’s LineTotal values.
  • Only delivered orders count. An order’s Status is Delivered, Returned or Cancelled, and returned and cancelled orders are left out.
  • Shipping is not sales. ShippingAmount is left out. There is no sales tax in the store.
  • The date is the order date. An order belongs to the day in OrderDate, whenever it shipped.

Step 4

Answer four questions.

The queries are plain SQL, with nothing particular to one database. They assume each file is loaded into a table of the same name.

1. What were net sales each year?

Orders joined to the calendar
SELECT c.Year, SUM(o.Subtotal - o.DiscountAmount) AS NetSales
FROM StoreOrders o
JOIN CalendarDates c ON c.CalendarDate = o.OrderDate
WHERE o.Status = 'Delivered'
GROUP BY c.Year
ORDER BY c.Year;

Group by c.MonthName as well for the monthly report, or by c.FiscalYear and c.FiscalQuarter for the federal fiscal year.

2. Which product categories sell the most?

Order lines joined to orders and products
SELECT p.Category, SUM(i.LineTotal) AS NetSales
FROM StoreOrderItems i
JOIN StoreOrders o ON o.ID = i.OrderID
JOIN StoreProducts p ON p.ID = i.ProductID
WHERE o.Status = 'Delivered'
GROUP BY p.Category
ORDER BY NetSales DESC;

3. How many customers bought, and where do they live?

Orders joined to people
SELECT pe.StateName, COUNT(DISTINCT o.PersonID) AS Customers
FROM StoreOrders o
JOIN People pe ON pe.ID = o.PersonID
WHERE o.Status = 'Delivered'
GROUP BY pe.StateName
ORDER BY Customers DESC, pe.StateName;

4. How much is sold on weekends and holidays?

The calendar’s business-day flag
SELECT SUM(o.Subtotal - o.DiscountAmount) AS NetSales
FROM StoreOrders o
JOIN CalendarDates c ON c.CalendarDate = o.OrderDate
WHERE o.Status = 'Delivered' AND c.Year = 2025
  AND c.IsBusinessDay = 0;

IsBusinessDay is false on weekends and on observed federal holidays. The files write it as true or false, so if your database loaded it as text, compare it with 'false'.

Step 5

Check your results.

The store is fixed, so these numbers do not change. The first column is what the free samples give. The second is the complete data.

Rows, before any sums

CountFree samplesComplete data
Orders133168,314
Order lines242340,002
Delivered orders120155,371
Returned orders1110,462
Cancelled orders22,481

1. Net sales by year

YearFree samplesComplete data
2022$2,722.96$3,074,025.23
2023$2,446.32$3,641,394.46
2024$2,141.01$4,615,040.73
2025$4,105.52$5,350,876.70
All four years$11,415.81$16,681,337.12

2. Net sales by category

CategoryFree samplesComplete data
Home & Kitchen$2,781.97$4,085,999.44
Electronics$2,024.60$3,580,247.70
Apparel$1,429.87$2,399,473.29
Outdoors$1,780.60$2,228,366.13
Sports & Fitness$1,641.19$1,827,829.32
Beauty & Personal Care$785.23$975,951.99
Pets$534.02$872,379.68
Office & School$438.33$711,089.57
All categories$11,415.81$16,681,337.12

The categories add up to the same total as the years. If yours do not, a join is dropping or repeating rows.

3. Customers

MeasureFree samplesComplete data
Customers with a delivered order1518,593
The three states with the mostTexas 3, Maryland 2, Washington 2Texas 2,074, California 1,959, Florida 1,496

4. Weekends and holidays in 2025

MeasureFree samplesComplete data
Net sales on weekends and federal holidays$1,080.62$1,728,835.39
Share of the year’s net sales26.3%32.3%

When the samples are not enough

Run it on the complete data.

The samples prove the joins. The complete data gives a report worth showing: 168,314 orders over four years, with growth each year, peaks on Black Friday and Cyber Monday, promo codes, returns and cancellations. All five files come with the Data Pass, along with every other collection.

Need a different shape of data? The test data generator makes customers with orders and products to your own size and columns.

Working with the files? Inspect a CSV, join two CSV files, convert CSV to JSON, or generate SQL inserts with the free tools.