Have you ever wondered how shops such as Amazon manage to give such highly accurate guesses about what you may want to buy the next time you visit them? Those self tuning services are a great source of income to those companies. I myself have bought more than a few items based on those recommendations. It turned out that it is not really hard to get some pretty amazing results, if you are willing to consider the fact that people usually fall over themselves giving you the data that you can use.

Let us take this innovative schema for a spin, shall we?

I want to display a list of suggest products whenever a customer submit an order. But how do I do this? I don't want to spend a lot of time analyzing the products and matching them to customer preferences. A good point to start is with noting that humans are generally predictable, so if I liked bought a certain product, and you bought a certain product, it is likely that I'll be interested in something that you bought.

This observation, along with the table structure above, leads me very quickly to this query:

SELECT TOP 15 [Id], [Name]

FROM Products suggested WHERE suggested.Id IN (

            SELECT ProductId FROM OrderLines WHERE OrderId IN (

                  SELECT other.Id FROM Orders other

                        WHERE EXISTS (SELECT 1 FROM OrderLines line

                              WHERE line.OrderId = other.Id AND line.ProductId

                              IN (SELECT line2.ProductId FROM OrderLines line2

                                          WHERE line2.OrderId = @OrderId))))

      AND suggested.Id NOT IN (

            SELECT line2.ProductId FROM OrderLines line2

                                    WHERE line2.OrderId = @OrderId)
GROUP BY Id, [Name]
ORDER BY COUNT(id)

This query simply pulls all the products that were ordered by customers that ordered a product that I just bought. It then returns the 15 most popular products. This query even aims for relevance, since it will only pull out the products that were bought on the same order as products in the order I just purchased.