The Bestseller Ranking

The storefront at Whetcode Supply has a Bestsellers shelf, and the position on it is printed beside each item rather than left for the shopper to count down the page. Position is decided by units shifted, not by money — a shelf is about what people buy, and the cheap thing everybody buys belongs at the top of it.

Two items have shifted exactly the same number of units, and the shop's rule is that equal sales mean an equal position: they share a number, and the position immediately after it is skipped. A shelf can therefore read 1, 2, 3, 4, 4.

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.

products — the catalogue, one row per item on sale. price is what a single unit costs, in dollars.

The tables already exist in the database — there is nothing to create or load.

Task: Write a query returning three columns, name (the product's name), units (the total units of that item sold across every order) and sales_rank (its position on the shelf by units, 1 being the most sold), one row per product that has sold at least once. Two items on the same units share a position and the next position is skipped. Order the rows by sales_rank ascending, and alphabetically by name for two items sharing a position. A product nobody has ordered does not appear at all.

Example output — shape only, on an invented catalogue. The second and third moved the same units, so they share position 2 and position 3 goes unused: