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. Keep the whole SQL in the string query and do not run or print it yourself. After your program, the check calls show(db, query), which runs the query and prints the header, the rows and the row count. The units of each item are in order_items.quantity.
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.
Free plan: 30 tutor messages. PRO is unlimited.
