How to rank a customer according to a next best offer model in SQL in case the customer is elegible for more than one product

Question

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.

Answer 1

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

1. You join your Customer and Product table by customer Id.
2. Then pivot the products by their suitability.
3. Finally, join back by the pivoted product. The rank then becomes a simple ROW_NUMBER