Ranking Customers by Typical Order Size

Whetcode Supply is rebuilding its VIP programme. The old version picked a single "top customer" by total spend, and it kept crowning the same person for placing a lot of small orders. What the team wants this time is a full board built on typical basket size — how big a customer's average order is, rather than how many they placed.

Two things stand between you and that number. An order row records units bought but not their price, which lives on the product. And one customer's average will not divide evenly, so the answer carries a repeating decimal.

orders — one row per order. quantity is units bought. product_id points at a row in products.

products — one row per product. price is the price of a single unit, in dollars.

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

Task: Write a query returning three columns, customer_id, avg_order_value and value_rank, with one row per customer who has placed at least one order. avg_order_value is the mean value of that customer's orders. An order's value is its quantity multiplied by the unit price of the product ordered. It is not rounded — one customer's figure prints as a long repeating decimal, and that is the expected answer. value_rank is their position on the board, 1 for the largest average. Two customers on exactly equal averages share a position and the position after it is skipped. Rows come back sorted by value_rank, best position first, and two customers sharing a position by customer_id.

Example output — shape only, on invented customers. Nothing is rounded, so an average that does not divide cleanly prints its full repeating tail, and the two customers level on it share a position.