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.
Net sales by year
Delivered orders, after discounts, without shipping.
| Year | Complete 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.
| File | One row per | Collection | Free sample |
|---|---|---|---|
StoreOrders | Order | Sample store | Download the three files (CSV) |
StoreOrderItems | Product on an order | ||
StoreProducts | Product and color | ||
People | Customer | People | Download (CSV) |
CalendarDates | Day | Calendar table | Download (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 column | Matches | Meaning |
|---|---|---|
StoreOrderItems.OrderID | StoreOrders.ID | Each line belongs to one order. |
StoreOrderItems.ProductID | StoreProducts.ID | Each line is one product. |
StoreOrders.PersonID | People.ID | Each order was placed by one person. |
StoreOrders.OrderDate | CalendarDates.CalendarDate | Each 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 - DiscountAmounton the order. It equals the sum of that order’sLineTotalvalues. - Only delivered orders count. An order’s
Statusis Delivered, Returned or Cancelled, and returned and cancelled orders are left out. - Shipping is not sales.
ShippingAmountis 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?
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?
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?
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?
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
| Count | Free samples | Complete data |
|---|---|---|
| Orders | 133 | 168,314 |
| Order lines | 242 | 340,002 |
| Delivered orders | 120 | 155,371 |
| Returned orders | 11 | 10,462 |
| Cancelled orders | 2 | 2,481 |
1. Net sales by year
| Year | Free samples | Complete 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
| Category | Free samples | Complete 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
| Measure | Free samples | Complete data |
|---|---|---|
| Customers with a delivered order | 15 | 18,593 |
| The three states with the most | Texas 3, Maryland 2, Washington 2 | Texas 2,074, California 1,959, Florida 1,496 |
4. Weekends and holidays in 2025
| Measure | Free samples | Complete data |
|---|---|---|
| Net sales on weekends and federal holidays | $1,080.62 | $1,728,835.39 |
| Share of the year’s net sales | 26.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.