locked
How to do product suggestion like that of amazon? RRS feed

  • Question

  • User-1415763228 posted
    Such as customers who bought this also bought blah blah. Is there any existing framework that helps us do this? Thank you!
    Sunday, February 17, 2013 11:47 AM

Answers

  • User-821857111 posted

    The obvious way of doing this is to query the details of orders that included the current item being viewed. For example, the following SQL will return the top five other products that were ordered alongside product 59 (Raclette Courdavault) from the Northwind sample database:

    SELECT ProductName FROM Products WHERE ProductID IN (
    	SELECT TOP 6 ProductID From [Order Details] WHERE OrderID IN (
    		SELECT OrderID From [ORDER Details] WHERE ProductID = 59
    		)
    	Group By ProductID ORDER BY Count(ProductID) DESC
    ) AND ProductID <> 59
    

    The result will be the most frequently purchased products alongside Raclette Cordavault (whatever that is).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 17, 2013 3:55 PM

All replies

  • User-821857111 posted

    The obvious way of doing this is to query the details of orders that included the current item being viewed. For example, the following SQL will return the top five other products that were ordered alongside product 59 (Raclette Courdavault) from the Northwind sample database:

    SELECT ProductName FROM Products WHERE ProductID IN (
    	SELECT TOP 6 ProductID From [Order Details] WHERE OrderID IN (
    		SELECT OrderID From [ORDER Details] WHERE ProductID = 59
    		)
    	Group By ProductID ORDER BY Count(ProductID) DESC
    ) AND ProductID <> 59
    

    The result will be the most frequently purchased products alongside Raclette Cordavault (whatever that is).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 17, 2013 3:55 PM
  • User-1415763228 posted

    The obvious way of doing this is to query the details of orders that included the current item being viewed. For example, the following SQL will return the top five other products that were ordered alongside product 59 (Raclette Courdavault) from the Northwind sample database:

    SELECT ProductName FROM Products WHERE ProductID IN (
            SELECT TOP 6 ProductID From [Order Details] WHERE OrderID IN (
                    SELECT OrderID From [ORDER Details] WHERE ProductID = 59
                    )
            Group By ProductID ORDER BY Count(ProductID) DESC
    ) AND ProductID <> 59
    

    The result will be the most frequently purchased products alongside Raclette Cordavault (whatever that is).

    Thank you very much. That's definitely a good idea.  I have been wondering if there is a 3rd party framework that has such functionalities built in.

    Sunday, February 17, 2013 5:16 PM