Comment by dennisgorelik

Could anyone with access to HN comments voting data execute something similar to the SQL query below and evaluate the results?

The idea is to use past voting correlation with other users to sort the comments.

  declare @userId int;
  select @userId = UserId
  from users
  where Username = 'pg';

  /* Let's calculate expert table first. We will use it to rate comments later. */
  select
	v2.UserId,
	sum(v1.Score * v2.Score) as VotingCorrelation
  into #expert
  from CommentVotingLog v1
  inner join CommentVotingLog v2
	on v2.CommentId = v1.CommentId
  where v1.UserId = @userId
  group by v2.UserId;

  /* Now we can rate comments against #expert table: */
  select
	c.CommentText,
	(select sum(v.Score * e.VotingCorrelation)
	from CommentVotingLog v
	inner join #expert e
		on e.UserId = v.UserId
	where v.CommentId = c.CommentId
	) as Rating
  from Comment c
  where c.ArticleId = 4692598 -- or another article that's discussed
  order by Rating desc;

I assume CommentVotingLog table has CommentId, UserId of the voter, and Score that voter gave to the comment: +1 for upvote or -1 for downvote)

I also assume that CommentVotingLog table has at least one record for every comment -- the author of that comment gives Score = +1 to that comment.

These queries don't have "freshness" adjustment (older comments had higher change to get upvoted, so their rating should be somewhat downgraded).