I have a set of customers that are eligible for a certain type of products:
| Customer | Product |
|---|---|
| John | Banana |
| John | Apple |
| John | Orange |
| Carla | Banana |
| Carla | Apple |
| Carla | Orange |
I also have a model which tells me which products are most suited for the customer, based on his preferences:
| Customer | Most Suited Product | Second Most Suited Product | Third Most Suited Product |
|---|---|---|---|
| John | Peach | Orange | Banana |
| Carla | Apple | Banana | Peach |
As you can see, there may be other products most suited, but they aren't eligible at the moment.
I'm running a campaign and I can only advertise two products per customer. This is down through an outbound sales team which requires me to have duplicates on my dataset.
How would I rank them according to their product most suited in order to have only two rows per customer?
I want this:
| Customer | Product | Rank |
|---|---|---|
| John | Orange | 1 |
| John | Banana | 2 |
| Carla | Apple | 1 |
| Carla | Banana | 2 |
I haven't tried anything yet.
You can do something like this:
SELECT customers.Customer, p.Product
, ROW_NUMBER() OVER(PARTITION BY customers.Customer ORDER BY p.sort) AS rank
FROM (
VALUES (N'John', N'Banana')
, (N'John', N'Apple')
, (N'John', N'Orange')
, (N'Carla', N'Banana')
, (N'Carla', N'Apple')
, (N'Carla', N'Orange')
) customers (Customer,Product)
INNER JOIN (
VALUES (N'John', N'Peach', N'Orange', N'Banana')
, (N'Carla', N'Apple', N'Banana', N'Peach')
) products (Customer,[Most Suited Product],[Second Most Suited Product],[Third Most Suited Product])
ON products.Customer = customers.Customer
CROSS APPLY (
VALUES ([Most Suited Product], 1),([Second Most Suited Product],2),([Third Most Suited Product],3)
) p (product, sort)
WHERE p.product = customers.Product