Where do our orders come from?
- O(n) · Medium
- Full plan
- SQL
- grouping
- joins
- nulls
Problem
The carrier charges by city, and before sitting down to negotiate, the owner wants to know where the store's orders come from. For each city that at least one order has come from, show the city, then how many orders, then how many units. Two customers in the same city add up in the same row, and the orders from anyone with no city on file go together in one row labeled No city. A city whose customers have never ordered isn't on the list.
The order dated the 20th counts: the date is wrong, but the package still has to ship.
The city with the most orders goes first; on a tie, the one with more units.
What it has to return
These rows, in this order
| city | how_many | units |
|---|---|---|
| Denver | 5 | 8 |
| Austin | 3 | 8 |
| Boston | 2 | 2 |
| Tampa | 1 | 10 |
| No city | 1 | 2 |
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 c.city, o.quantity
FROM orders o
JOIN customers c
ON o.customer_id = c.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.