I'm looking at movie ratings across a friend group and I'm trying to find the best way to measure how people rate movies compared to IMDb ratings.
Here you can see my table titled fRating, which is a fact table that contains the MovieID, the RaterID, and their UserRating:
This joins with dMovies, which is the dimension table for all of the movies. That table is shown below:
Now, how would I go about finding how much each rater varies from the IMDb ratings? Here is my attempt, using STDDEV:
SELECT a.[RaterID]
,Rater
,STDEV(([UserRating] - ROUND(b.IMDb_Rating,0))) as StdDev
FROM [IMDbRatings].[dbo].[fRating] a
JOIN [IMDbRatings].dbo.dMovies b ON a.MovieID = b.MovieID
JOIN [IMDbRatings].dbo.dRaterID c ON a.RaterID = c.RaterID
GROUP BY a.RaterID, Rater
Here are the results that I get from that:
Am I on the right track? Is this doing what I want it to do?
Thank you for your wisdom!