Reputation per tag per User[Considering TagSynonyms also]

0

Please login or register to vote for this query.

(click on this box to dismiss)

Genealogy and Family History Meta

Q&A about the site for expert genealogists and people interested in genealogy or family history

-- Replace the user-id in the query as per the requirement

select UserId,displayname,Tag,
Sum(Questions_Or_Answers) Number_of_QuestionsOrAnswers
,Sum(Reputation) TotalReputation,
CONVERT(decimal(12,3),Sum(Reputation)) / CONVERT(decimal(12,3),
SUm(Questions_Or_Answers)) As ReputationPerPost
from
(select UserId ,displayname,tagname Tag, 
Questions_Or_Answers,Reputation 
from 
(select u.id 'UserId',u.displayname,
case when ts.TargetTagName is not null
then ts.TargetTagName
Else t.Tagname End 'Tagname'
,
count(p.Id) As 'Questions_Or_Answers'
,(Sum(case when v.VoteTypeId=2 then 1 else 0 end)*10 -
Sum(case when v.VoteTypeId=3 then 1 else 0 end) *2 )as 'Reputation'
from users as u
inner join posts as p on u.id=p.owneruserid
--inner join posts as q on p.id=q.parentid
JOIN PostTags AS pt ON pt.PostId = p.Id
JOIN Tags AS t ON t.Id = pt.TagId
Left join tagsynonyms ts on t.Tagname=ts.SourceTagName 
INNER JOIN Votes AS v ON v.PostId = p.Id
where p.posttypeid in (1,2)
and v.VoteTypeId in (2,3)
group by u.id,u.displayname,t.tagname,ts.TargetTagName
Union
select u.id 'UserId',u.displayname,
case when ts.TargetTagName is not null
then ts.TargetTagName
Else t.Tagname End 'Tagname'
,
count(p.Id) As 'Questions_Or_Answers'
,(Sum(case when v.VoteTypeId=2 then 1 else 0 end)*10 -
Sum(case when v.VoteTypeId=3 then 1 else 0 end) *2 )as 'Reputation'
from users as u
inner join posts as p on u.id=p.owneruserid
--inner join posts as q on p.id=q.parentid
JOIN PostTags AS pt ON pt.PostId = p.parentid
JOIN Tags AS t ON t.Id = pt.TagId
Left join tagsynonyms ts on t.Tagname=ts.SourceTagName 
INNER JOIN Votes AS v ON v.PostId = p.Id
where p.posttypeid in (1,2)
and v.VoteTypeId in (2,3)
group by u.id,u.displayname,t.tagname,ts.TargetTagName) a
where Userid in (
101651)
) a
Group by UserId,displayname,Tag
order by UserId

Enter Parameters

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