Interest groups, user affinity Tagvector

2

Please login or register to vote for this query.

(click on this box to dismiss)

Emacs

Q&A for those using, extending or developing Emacs

-- How many upvotes do user have for each tag?
-- Translating to a "merit metric" and comparing two users. 


DECLARE @UserId1 int = ##UserId1##

DECLARE @UserId2 int = ##UserId2##  

SELECT TOP(200)
   TagName,
   round(
   100*min(merit)*(AVG(merit/tot_user_merit)-STDEVP(merit/tot_user_merit)) /(AVG(merit/tot_user_merit)+STDEVP(merit/tot_user_merit)),
     0) AS affinity_index,
   min(merit) as merit_min,
   round(min(merit/tot_user_merit),4) as merit_factor_min ,
   round(max(merit/tot_user_merit),4) as merit_factor_max 
   
  -- max(merit) as merit_max, 
  -- max(merit/tot_user_merit) as factor_max, 
  -- min(merit/tot_user_merit) as factor_min 
   
FROM (
  SELECT
      Posts.OwnerUserId AS userId,
      TagName, 
      round(sqrt(COUNT(*)),0) AS merit -- quadratic voting metric

  FROM Tags
      INNER JOIN PostTags ON PostTags.TagId = Tags.id
      INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
      INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 2
  WHERE 
      Posts.OwnerUserId = @UserId1 OR Posts.OwnerUserId = @UserId2
  GROUP BY Posts.OwnerUserId, TagName 
  ) tt INNER JOIN  (

    SELECT avg(merit) avg_user_merit, sum(merit) tot_user_merit, 
          OwnerUserId 
    FROM (
      SELECT
          round(sqrt(COUNT(*)),0) AS merit, Posts.OwnerUserId 
      FROM Tags
          INNER JOIN PostTags ON PostTags.TagId = Tags.id
          INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
          INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 2
      WHERE 
      Posts.OwnerUserId IN (@UserId1, @UserId2)
      GROUP BY TagName , Posts.OwnerUserId
      having count(*)>1
     ) t9
     GROUP BY OwnerUserId
  ) tot1
  ON tot1.OwnerUserId=tt.userId 
GROUP BY TagName 
HAVING count(*)>1
ORDER BY affinity_index DESC, TagName

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results