DO YOU GIVE WHAT YOU GET?

2

Please login or register to vote for this query.

(click on this box to dismiss)

Comparison of (% of votes that are upvotes) for VOTES CAST versus VOTES RECEIVED for the specific user(s).

Server Fault

Q&A for system and network administrators

--Comparison of (% of votes that are upvotes) for VOTES CAST versus VOTES RECEIVED for the specific user(s).


----Votes received by user (VOTES table)
select 
  a.owneruserid as UserID,
  a.owneruserid as [User Link],
  sum(case when v.votetypeid=2 then 1 else 0 end) as [UpVotes],
  sum(case when v.votetypeid=3 then 1 else 0 end) as [DownVotes],
  cast(sum(case when v.votetypeid=2 then 1 else 0 end) as decimal) / sum(1) as rUpVotepercent
into #votesReceivedByUser
from 
  posts a left outer join votes v
  on v.postid = a.id
where a.owneruserid in (##UserID##)
  and v.votetypeid in (2,3)
  and a.posttypeid=2
group by
  a.owneruserid


--Votes CAST by User (USERS table)
select 
  a.owneruserid as [UserID],
  count(*) as [#VBAanswers]
into #VBAanswerers
from posts q join posts a
on q.id = a.parentid
where 
  a.posttypeid = 2
  and a.owneruserid in (##UserID##)
  and a.creationdate >= dateadd(d,-14,(select max(creationdate) from posts))
group by a.owneruserid
having count(*)>5
------------------------------
select 
  userid,
  sum(views) as [#ProfileViews], 
  sum(upvotes) as [#Upvotes], 
  sum(downvotes) as [#DownVotes],
  case when sum(upvotes+downvotes) = 0 then 0 else cast(sum(upvotes) as decimal) / sum(upvotes+downvotes) end as [pUpVote]
into #votesCastByUser
from users u join #VBAanswerers vbau
on u.id = vbau.UserID
group by userid
order by sum(views) desc
---------------------------
select
  rank() over ( order by vc.[pUpvote] desc) Rank,
  vc.userid as [User Link], 
  --vc.[#ProfileViews], 
  vc.[#Upvotes] as CastUp, 
  vc.[#Downvotes] as CastDown, 
  format(vc.[pUpVote],'0.0%') as [CastUpPercent],
  vr.upvotes as UpVotesRec, vr.downvotes as DownVotesRec,
  format(rUpVotepercent,'0.0%') as ReceivedUpPercent
from #votesCastByUser vc join #votesReceivedByUser vr
on vc.userid = vr.userid


--UserID:Enter one or more numeric User ID's:"Use commas to separate multiple User ID's."

Enter Parameters

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