WEBVTT 00:00:00.350 --> 00:00:02.419 Welcome to this optional exercise. 00:00:02.599 --> 00:00:07.250 We will reuse group by to find the customer who spent the most on each product. 00:00:07.430 --> 00:00:11.078 Then we will turn that calculated ID into a relationship. 00:00:11.258 --> 00:00:16.877 You can skip this exercise and continue with writes and transactions; no later lesson needs 00:00:16.877 --> 00:00:17.679 this formula. 00:00:19.050 --> 00:00:20.757 Why do we group before sorting? 00:00:20.937 --> 00:00:26.569 Imagine Ada buys this product twice, spending six each time, while Grace spends ten once. 00:00:26.749 --> 00:00:29.223 The largest single purchase belongs to Grace. 00:00:29.403 --> 00:00:32.155 But Ada's combined twelve makes her the best buyer. 00:00:32.335 --> 00:00:36.602 Group by combines her purchases before order by and limit choose a winner. 00:00:36.782 --> 00:00:40.409 We use the grouping from lesson six to choose the winning customer. 00:00:41.800 --> 00:00:43.891 Let's find each product's best buyer. 00:00:44.071 --> 00:00:48.807 Best means the customer who spent the most on this product across all their invoices. 00:00:48.987 --> 00:00:54.363 Group its rows by customer, sum each customer's spending, then sort and keep one ID. 00:00:54.543 --> 00:00:58.788 Sorting individual rows would find the largest single purchase instead. 00:00:58.968 --> 00:01:02.125 If totals are equal, the lower customer ID wins. 00:01:03.500 --> 00:01:05.591 Let's connect that ID to the customer. 00:01:05.771 --> 00:01:08.715 A weak relation starts from the calculated column. 00:01:08.895 --> 00:01:10.772 It creates no stored foreign key. 00:01:10.952 --> 00:01:13.704 Reload the model and refresh the table handles. 00:01:15.034 --> 00:01:16.933 Let's read the buyer's name and city. 00:01:17.113 --> 00:01:20.227 The path starts from our formula, just like a stored ID. 00:01:20.407 --> 00:01:25.570 Linus is the best buyer of biscuits and black tea; Grace is the best buyer of bottles. 00:01:25.750 --> 00:01:28.929 With no sales, the ID and customer fields are null. 00:01:29.109 --> 00:01:31.711 True confirms that no migration is needed. 00:01:33.100 --> 00:01:34.593 Thanks for following along! 00:01:34.773 --> 00:01:40.525 We combined purchases, selected one winning customer, and followed a weak relation from a 00:01:40.525 --> 00:01:41.429 calculated ID. 00:01:41.609 --> 00:01:44.660 This optional extension adds no stored columns. 00:01:44.840 --> 00:01:49.875 Continue with writes and transactions; that lesson also works without this exercise.