Clean and summarize a sales export
Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.
- Level
- beginner
- Time
- ~10 min
- Task
- Build the query
- Access
- Free — no account needed
Loading the practice workspace…
About this exercise
What you'll practise
Build a repeatable cleanup pipeline: explicit types, text cleanup, row filtering, deduplication, a calculated column and a grouped total.
Northwind Kites exports orders from its till system every week. The export has inconsistent region spelling, a duplicated order and an order with no quantity. The operations lead wants revenue by region from completed orders only.
Steps
- Use the first row as headers.
- Set types:
OrderIDstays text (keep leading zeros),Qtywhole number,UnitPricedecimal number, using the en-US culture. - Clean
Region: trim spaces and convert to UPPERCASE. - Keep rows where
StatusisCompleteandQtyis not null. - Remove exact duplicate rows.
- Add
Revenue=Qty×UnitPrice. - Group by
Regionwith the sum ofRevenuein a column namedRevenue.
Your result must
- Output columns: Region, Revenue
- One row per region (NORTH, SOUTH)
- The duplicated order is counted once
- Works when new orders are added to the export
Input
- SalesExport (file)
- Text file; first line: OrderID,Region,Qty,UnitPrice,Status
- Every field is text until you set types
Expected output columns
Region (text), Revenue (number)
| Region | Revenue |
|---|---|
| NORTH | 33 |
| SOUTH | 10 |