Skip to the content

Not the priciest, not the biggest

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

categoryhow_manyhighest
Accessories699.5
Audio2119.8
Devices2899
Displays2378

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.id
Solve this challenge

It 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

See all challenges →