Helps a user gauge community impression.
Q&A about the site for serious players and enthusiasts of chess
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