Each Customer's Favorite Product

An interviewer asks: what is each customer's favorite product, the one they bought the most units of? This is the "top 1 per group" question: add up the units per customer and product, number each customer's products from the most bought, and keep number 1.

ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) numbers the rows 1, 2, 3… inside each group and starts again at 1 for the next group. For example, this numbers each customer's orders from the latest; a query around it that keeps n = 1 gives each customer's latest order:

SELECT customer_id, id, ordered,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered DESC) AS n
FROM orders

Unlike RANK, ROW_NUMBER never gives two rows the same number, so each customer gets exactly one row 1. When two products have the same units, a second sort column, the product name, decides which comes first. The hidden setup adds one more paid order for Dan (order 8: 2 units of product 7), so two of his products tie at 2 units.

The query must print:

customer | product | units
Ana Pop | Pen | 15
Ben Ortiz | Lamp | 1
Chloe Ng | Mug | 5
Dan Weber | Stapler | 2
(4 rows)

Your task: the named query bought already has its FROM and joins. Give it the two names and the summed units, one group per customer and product. Then add a second named query, ranked, that numbers each customer's products as product_rank, and keep the rows numbered 1.

Assignment
Follow the Coding Tutorial to build bought, ranked and the main query, one clause per step.

Hint
Both customers and products have a name column: write the table in front and rename each with AS. Sort by units, highest first, then by product name, so a tie goes to the name that comes first.