Not the priciest, not the biggest
- O(n) · Medium
- Full plan
- SQL
- grouping
- joins
Problem
A package that gets lost in transit is on the store, and the owner wants to know the most that has ever been riding on a single one: what the priciest order in each section came to. An order is worth its product's price times the units ordered, so the priciest order doesn't have to be the one for the priciest product, or the one with the most units.
For each category with at least one order, show the category, then how many orders it has received, then what the priciest one is worth, rounded to 2 decimals. The order dated the 20th counts: the date is mistyped, but the order exists.
What it has to return
These rows, in any order
| category | how_many | highest |
|---|---|---|
| Accessories | 6 | 99.5 |
| Audio | 2 | 119.8 |
| Devices | 2 | 899 |
| Displays | 2 | 378 |
This is what comes out with the data in the practice database this challenge uses. The column names don’t count, so name yours however you like: what counts is how many there are and the order they come in. Besides this one, the challenge has hidden tests with other data that are revealed when you submit your solution.
You start with this
SQL
SELECT p.category, p.price,
o.quantity
FROM orders o
JOIN products p
ON o.product_id = p.idIt opens in your browser, with the editor and the tests. This challenge is part of the full plan; the O(1) and O(log n) ones are free.
More O(n) challenges
- Pages carried homeSQL · grouping · joins · decimals
- Same book twice doesn't countSQL · grouping · sorting
- The 250 ClubSQL · grouping · joins · decimals
- The notebook by the registerSQL · grouping · joins
- What the team makesSQL · grouping · joins · decimals
- Where are the books from?SQL · grouping · joins · sorting