Listing 1: Create Database Table

CREATE TABLE CollabRatings (
	UserID 	int(8),
	ItemID 	int(8),
	Rating 	float
)

PRIMARY: UserID,ItemID

Listing 2: Determine Community of Like Users Using Mean Squared Differences

<cfset userID = 1>

<cfquery name="getNearestNeighbors" datasource="CF">
SELECT TOP 5 MIN(c.UserID) AS OtherUserID, AVG(POWER(ABS(a.Rating-c.Rating),2)) AS MSD
FROM CollabRatings a, Users b, CollabRatings c
WHERE a.UserID=b.UserID AND b.UserID = #userID#
AND c.ItemID=a.ItemID AND c.UserID<>a.UserID
GROUP BY c.UserID
ORDER BY MSD ASC
</cfquery>
	
Note: It's a good idea to use TOP X in your query to to improve scalability

Listing 3: Assigning Weights to Other Items Ranked by Community Members

<cfquery name="getList" datasource="CF">
SELECT ItemID, (1/#NumberFormat(MSD,"00.0000000")#) AS MSDCalc, Rating, 
Rating*(1/#NumberFormat(MSD,"00.0000000")#) as WeightedRating
FROM CollabRatings2
WHERE UserID=#OtherUserID#
ORDER BY WeightedRating DESC
</cfquery>

Listing 4: CollabCalcTable for Aiding Calculations

CREATE TABLE CollabCalcTable (
	CalcID			int
	ItemID 		int,
	MSD			float,
	Rating 		float,
	WeightedRating 	float
)

PRIMARY: CalcID - generated randomly using <cfset CalcID = RandRange(1000000,9999999)>

Listing 5: Get Final Weighted Averages

<cfquery name="getWeightedAverages" datasource="#request.dbsource#"> 
SELECT TOP 3 MAX(b.ItemIdentifier) AS ItemIdentifier, MAX(b.ItemName) AS ItemName,
SUM(a.WeightedRating)/SUM(a.MSD) AS PredictedRating
FROM CollabCalcTable a, CollabItems2 b
WHERE CalcID=#iCalcID# AND a.ItemID=b.ItemID
AND a.ItemID NOT IN (SELECT ItemID FROM CollabRatings2 WHERE 
  UserID=#getUserID.UserID#)
GROUP BY a.ItemID
ORDER BY PredictedRating DESC
</cfquery>

Note: We USE TOP X equal to the number of predictions we'd like

Listing 6: Final Recommendations

Color	Weighted Average
Blue	4.333
Red	4.000
Yellow	2.333

Listing 7: Most Items Ordered Along with Another Item

<cfquery name="MostItemsOrdered" datasource="CF" >
SELECT TOP 3 OrderItems.ItemID,Items.Title,count(OrderItems.ItemID) as ItemSaleCount
FROM OrderItems,Items
WHERE OrderID IN (SELECT OrderID FROM OrderItems WHERE ItemID = 3)
AND OrderItems.ItemID != 3
AND OrderItems.ItemID = Items.ItemID
GROUP BY OrderItems.ItemID,Items.Title
ORDER BY ItemSaleCount DESC
</cfquery>

Note: The TOP X value determines how many similar items to recommend