Hello.
I have two tables. One with clients. One with sales.
I want to create 3 columns in my clients table.
First column: Name of the product in which they spent the most money.
Second column: Name of the product in second place.
Third column: Name of the product in third place.
To clarify: I need the results to show the product in which the client spent the most money overall. So, for example, if a client spent 20€ on boots and 50€ on 10 t-shirts, it should say t-shirt, even tho boots are more expensive than t-shirts.
Besides that, I need to filter the sales table since I don't want all transactions to be considered. Let's say I want to filter by ProductCategory.
| ClientID | Name | LastName |
| 1 | John | Johnson |
| 2 | Mary | Smith |
| 3 | Peter | Miller |
| TransactionID | ClientID | ProductName | SalesValue | ProductCategory |
| 1 | 2 | Shirt | 5 | A |
| 2 | 1 | Sweater | 15 | C |
| 3 | 3 | Shirt | 5 | A |
| 4 | 3 | Pants | 10 | D |
| 5 | 2 | Socks | 1 | D |
| 6 | 1 | Boots | 20 | E |
| 7 | 1 | Shirt | 5 | A |
| 8 | 2 | Boots | 20 | E |
| 9 | 2 | Sweater | 15 | C |
Thank you in advance.
