Repeat Customers

Retention marketing at Whetcode Supply only writes to people who have already come back at least once — a customer who bought twice is worth a nudge, a customer who bought once is a different campaign entirely. The team wants the audience list with each person's order tally beside their name, so they can weigh up the top of the list by hand.

The tally is the awkward part. It is not stored anywhere: it comes into existence only once a customer's orders have been bundled together. And the test more than one has to be applied to that bundled figure, not to a single order row — no individual order knows, or could know, how many others the same person placed.

customers — one row per registered customer. city is the city given at signup and signup_date is the day they registered, written year-month-day.

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.

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

Task: Write a query returning two columns, name (the customer's name) and orders (how many orders that customer has placed), one row per customer with more than one order, most orders first, and alphabetically by name when two customers have the same tally.

Example output — shape only, on invented customers. The middle two are level, so they fall alphabetically: