5

Please login or register to vote for this query.

(click on this box to dismiss)

Helps a user gauge community impression.

Q&A about the site for professional robotic engineers, hobbyists, researchers and students

declare @UserID int = ##UserID##

declare @mainPosts int = (
  select 
    count(*) 
  from Posts 
  where 
    OwnerUserId = @UserID
    and PostTypeId = 2 -- answers
)

declare @upVotes int = (
  select
    count(*)
  from
    posts inner join
    votes on posts.Id = votes.PostId
  where 
    posts.OwnerUserId = @UserID
    and votes.VoteTypeId = 2 -- upvotes
    and ((posts.PostTypeId = 1) or (posts.PostTypeId = 2)) -- questions or answers
)

declare @downVotes int = (
  select
    count(*)
  from
    posts inner join
    votes on posts.Id = votes.PostId
  where 
    posts.OwnerUserId = @UserID
    and votes.VoteTypeId = 3 -- downvotes
    and ((posts.PostTypeId = 1) or (posts.PostTypeId = 2)) -- questions or answers
)

declare @totalClosedQuestions int = (
  select 
    count(*) 
  from Posts 
  where 
    OwnerUserId = @UserID
    and ParentId is null
    and ClosedDate is not null
)

declare @recentClosedQuestions int = (
  select 
    count(*) 
  from Posts 
  where 
    OwnerUserId = @UserID
    and ParentId is null
    and ClosedDate is not null
    and ClosedDate >= dateadd(year, -1, getdate())
)

declare @bounties int = (
  select
    count(*)
  from Votes
    inner join Posts on Votes.PostId = Posts.Id
  where
    OwnerUserId = @UserID
    and VoteTypeId = 9 -- bounty
)

declare @percentAnswersAccepted float = (
  select
    (cast(count(a.Id) as float) / nullif(@mainPosts, 0) * 100)
  from Posts q
    inner join Posts a 
      on q.AcceptedAnswerId = a.Id
  where
    a.OwnerUserId = @UserID
    and a.PostTypeId = 2 -- answers
)

declare @commentsUpVoted int = (
  select
    count(*)
  from Comments
  where 
    UserId = @UserID 
    and Comments.Score > 1 -- comments with at least 2 upvotes
)

select
  u.Reputation, 
  @percentAnswersAccepted as PercentAnswersAccepted,
  @upVotes as UpVotes,
  @downVotes as DownVotes,
  @totalClosedQuestions as ClosedQuestions,
  @commentsUpVoted as CommentsUpVoted2Plus,
  case 
    when 
      u.CreationDate >= dateadd(year, -1, getdate())
      and @upVotes < 3
      and @downVotes < 3
    then 'Too New to Say' 
    when 
      u.LastAccessDate <= dateadd(year, -1, getdate())
        or ((@upVotes < 2) and (@downVotes < 2))
    then 'Insufficient Activity' 
    when 
      (u.Reputation = 1
        or u.Reputation = 101)
      and @downVotes > @upVotes
      and (@downVotes - @upVotes) >= cast(((@upVotes + @downVotes) * 0.1) as float)
    then 'Negative'
    when 
      @upVotes < @downVotes 
      and (@downVotes - @upVotes) >= cast(((@upVotes + @downVotes) * 0.1) as float)
      and ((@percentAnswersAccepted < 25)
        or (@percentAnswersAccepted is null))
      and ((@recentClosedQuestions > 0)
        or (@upVotes < 2))
    then 'Negative' 
    when @upVotes > 0
      and (@downVotes >= cast(((@upVotes + @downVotes) * 0.33) as float)
        or abs(@upVotes - @downVotes) <= cast(((@upVotes + @downVotes) * 0.1) as float)
        or abs(@upVotes - @downVotes) < 5)
      and ((@percentAnswersAccepted < 33)
        or (@percentAnswersAccepted is null))
    then 'Mixed' 
    when 
      @upVotes > @downVotes 
      and @upVotes - @downVotes > 5
      and ((@percentAnswersAccepted < 33)
        or (@percentAnswersAccepted is null))
      and @bounties < 1
      and @recentClosedQuestions > 1
      and @commentsUpVoted < 1
    then 'OK' 
    when u.Reputation >= 1000
      and @upVotes > @downVotes
      and @downVotes < cast((@upVotes * 0.1) as float)
      and @percentAnswersAccepted > 33
      and u.LastAccessDate >= dateadd(year, -1, getdate())
      and @recentClosedQuestions < 1
      and @bounties > 1
      and @commentsUpVoted >= 10
    then 'Excellent' 
    else 'Positive' 
  end as [Overall Impression]
from Users u
where 
  u.ID = @UserID

fork query

created aug 8 13 danzek