Monthly sales consolidation
Larkspur Outdoor Supply (fictional)
Combine a folder of monthly till exports whose columns drift from month to month, then build the monthly revenue summary by region.
- Level
- intermediate
- Time
- ~50 min
- Deliverables
- 2
- Rubric
- v1
Access
Free. You can work through this project without an account; your queries stay in this browser. Sign in to save them to your account, resume on another device and record your score.
Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).
Business goal
Give the operations manager one refreshable table of every sales line and a monthly revenue-by-region summary that keeps working when next month's file lands in the folder.
Larkspur runs five outdoor-gear stores. Each month the till system drops a CSV into a shared folder. The exports were changed twice this year: in February the column order changed, and from March a Channel column (Store or Online) was added. Regional managers want monthly revenue, units and line counts by region. New stores sometimes open before the store list is updated.
Graded deliverables
1. Sales Lines
50 pointsOne typed row per sales line from every CSV in the folder, with the source file name.
- Columns: SourceFile, OrderID, OrderDate, StoreCode, SKU, Qty, UnitPrice, Channel
- Every .csv file in the folder is included, whatever its column order
- No empty rows; returns (negative Qty) are kept
- StoreCode trimmed and upper case
Expected output schema for Sales Lines Column Type SourceFile text OrderID text OrderDate date StoreCode text SKU text Qty whole number UnitPrice decimal Channel text (null before March) - Grain:
- One row per sales line in any CSV file in the folder.
- Order:
- Not graded.
- Nulls:
- Channel is null for lines from files that have no Channel column. No other nulls are expected.
2. Monthly Region Summary
50 pointsRevenue, units and lines by month and region, built on your Sales Lines query.
Builds on: Sales Lines
- Columns: Month, Region, Revenue, Units, Lines
- One row per month and region; returns reduce Revenue and Units
- Lines from stores missing in the store list are grouped under Unassigned
- Sorted by Month, then Region
Expected output schema for Monthly Region Summary Column Type Month date (first of month) Region text Revenue decimal Units whole number Lines whole number - Grain:
- One row per month and region that has at least one sales line.
- Order:
- Graded: Month ascending, then Region ascending.
- Nulls:
- No nulls. Unknown stores are reported as Region "Unassigned".