Exercise: Sales by Category
Write five aggregation pipelines for Riverbend Bike Shop's sales dashboard, from the revenue of each category to the three biggest orders, and let the tests run each one on a year of orders and check its rows.
Journeyman · MongoDB · Updated
Riverbend Bike Shop's dashboard has five empty tiles: the revenue of each category, the average order, the three biggest orders, how many orders each payment method took, and how many orders came to $1,000 or more. Each tile is one aggregation pipeline, and you'll write all five. Tests run your pipelines on the same 2,000 orders as this chapter's lessons and check every row they give back.
Get the exercise code
Open this lesson's GitHub repository, github.com/ask-codey/exercise-mongodb-sales-by-category, also linked from the Exercise box beside the lesson. Clone it into your mongodb-course folder. In VS Code, open the mongodb-course folder, open the terminal with Terminal > New Terminal, and type:
git clone https://github.com/ask-codey/exercise-mongodb-sales-by-category.git
Git makes a folder, exercise-mongodb-sales-by-category, with the exercise code inside. Open that folder in VS Code (File > Open Folder), open its terminal, and install the packages the tests use:
npm install
The folder holds:
queries.js: the five functions you'll finish.data/orders.json: Riverbend's 2,000 orders, the same onesriverbend-orders.jsbuilds in The Aggregation Pipeline: Data on a Conveyor Belt.data/load-into-mongosh.js: loads those orders into mongosh, so you can try a pipeline there first.test/: the tests, and the helper that runs your pipelines for them. You don't need to change either.
What to do
Every function in queries.js gets db, which works like it does in mongosh, and returns db.orders.aggregate([]) for now: a pipeline with no stages, which hands on all 2,000 orders unchanged. The comment above each function says exactly which rows it should give back:
/*
* 1. Revenue per category: one row for each category, holding the category in _id and the sum of its orders'
* totals in a field named revenue. Biggest revenue first.
*
* Gives 5 rows: bikes 181065, helmets 84364, lights 37935, locks 37272 and parts 23382. The first row is
* { _id: "bikes", revenue: 181065 }.
*/
export function revenueByCategory(db) {
return db.orders.aggregate([]);
}
Replace db.orders.aggregate([]) in each function with a pipeline of your own, after the word return. Each row must hold exactly the fields named here, in the order given:
revenueByCategory: 5 rows, one per category, each{ _id: <category>, revenue: <its orders' totals added up> }, biggest revenue first, as above.averageOrderValue: one row,{ average: 182.01 }: the averagetotalof all 2,000 orders (182.009), rounded to 2 decimal places, with no_id.biggestOrders: the 3 orders with the biggest totals, biggest first, each row holding only_idandtotal. When two totals are equal, the lower order number comes first, so the rows are orders 10690, 10755 and 11337, each with atotalof 2598.ordersByPayment: 3 rows, one per payment method, each{ _id: <method>, orders: <how many orders used it> }, most orders first: card 1196, paypal 599 and gift card 205.countBigOrders: one row,{ bigOrders: 94 }: the number of orders whosetotalis 1000 or more.
You can try each pipeline in mongosh first. In the exercise folder's terminal, start mongosh, type use sales_by_category to choose a practice database, then load("data/load-into-mongosh.js") to put the 2,000 orders in it. When a pipeline gives the right rows there, copy it into its function after return.
Check your work
Run the tests from the exercise folder:
npm test
At first every test fails. That's expected: each pipeline you finish turns its test green. When a test fails, its message says which rows it expected and shows the first rows your pipeline gave, for example 2) averageOrderValue should give one row holding only average: 182.01, the average total of all 2,000 orders (182.009) rounded to 2 decimal places, with no _id. Your pipeline gave 2000 rows: ...
The tests start their own throwaway MongoDB, so they never change your databases.
Hints
- Build one stage at a time. Run the first stage in mongosh, look at what it hands on, then add the next, as The Aggregation Pipeline: Data on a Conveyor Belt shows.
- "Per" means
$group. Per category and per payment method put a field path in_id; one answer for all the orders putsnullthere. $group: Totals, Counts and Averages has the accumulator for adding up, for averaging and for counting. - Groups come out in no particular order. "Biggest first" and "most orders first" need a
$sortafter the$group. - Rounding and leaving out
_idare shaping jobs. Shaping the Report: $project, $set, $sort and $limit shows$round, and the stage that chooses which fields a row keeps. - For a top three, the order of the stages matters. Five orders tie at 2598; a second sort field decides which three come first.
- Two ways to count. Inside
$group, a count is an accumulator; for a whole pipeline, there's a stage that counts.
When you're done
Every test is green, and npm test prints:
✔ 1) revenueByCategory gives the revenue of each of the 5 categories, biggest first (1190.1813ms)
✔ 2) averageOrderValue gives one row, { average: 182.01 } (36.9984ms)
✔ 3) biggestOrders gives orders 10690, 10755 and 11337, each with a total of 2598 (36.6046ms)
✔ 4) ordersByPayment gives card 1196, paypal 599 and gift card 205, most orders first (35.0063ms)
✔ 5) countBigOrders gives one row, { bigOrders: 94 } (37.6322ms)
ℹ tests 5
ℹ suites 0
ℹ pass 5
ℹ fail 0
ℹ cancelled 0
ℹ skipped 0
ℹ todo 0
ℹ duration_ms 1686.8499
The times in parentheses will be different on your computer. Now read each pipeline aloud, stage by stage, as the conveyor belt it is. If Riverbend asked for the revenue of only the card orders, per category, which stage would you add, and where?
Submit your exercise
The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here.
On GitHub, click +, then New repository. Name it (for example
exercise-mongodb-sales-by-category), choose Public, and click Create repository. Leave out the README: your folder already has one.In VS Code's terminal, in your exercise folder, send your work to your new repo. Point your copy at your own repo, then commit and push:
git remote set-url origin https://github.com/your-name/exercise-mongodb-sales-by-category.git git add . git commit -m "My solution" git push -u origin mainPaste your repo's address, such as
https://github.com/your-name/exercise-mongodb-sales-by-category, into Submit your exercise on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.
Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.
So what
Five tiles of a real dashboard, each one a pipeline you wrote and can read stage by stage. Reports like these are most of what a store, a school or a clinic asks of its data once it has a year of it.