Average Order Value
Every investor Whetcode Supply talks to asks for the same number early on: average order value, or AOV — what a typical order is worth. It is the figure that decides whether spending twenty dollars to win a customer makes sense, so the shop cannot quote it from memory or from a spreadsheet somebody built once.
It is not stored. An order row says which item and how many; the money lives in the catalogue. The single figure has to be built out of both tables.
orders — one row per order. customer_id is the id of the customer who placed it, product_id the id of the item bought, quantity how many units of that item, and order_date the day it was placed. An order row covers one item only.
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
products — the catalogue, one row per item on sale. price is what a single unit costs, in dollars.
| id | name | category | price |
|---|---|---|---|
| 1 | Wireless Mouse | electronics | 25.0 |
| 2 | Standing Desk | furniture | 350.0 |
| 3 | Desk Lamp | furniture | 40.0 |
| 4 | Mechanical Keyboard | electronics | 85.0 |
| 5 | Notebook Set | office | 12.0 |
The tables already exist in the database — there is nothing to create or load.
Task: Write a query returning one row with a single column, aov, holding the mean value of an order, an order's value being its item's price times its number of units. Round the finished mean to two decimal places, not each order on the way in.
Example output — the shape, on an invented figure. A shop averaging three hundred and twelve dollars seventy-five cents an order reads:
| aov |
|---|
| 312.75 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in