Qrep

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS

Q&A for administrators, end users, developers and designers for Craft CMS

--https://data.stackexchange.com/stackoverflow/query/145507/user-reputation-excluding-reputation-earned-on-a-certain-tag
-- should be larger than 1 because daily rep cap


DECLARE @rep_lower    int = 100000; 
DECLARE @rep_upper    int = 120000; 

-- SELECT
    --@MyAnswerUpped = COUNT(*)
    --select count(*) *10 as AnsRep, max(t1.Reputation) as TotRep ,t1.Id
    select 
        avg( nUp*10./Rep) as avgRepRatio,
        min( nUp*10./Rep) as minRepRatio,
        max( nUp*10./Rep) as maxRepRatio,
        avg(case when RepRatio >0.8  then 1. else 0. end) as Exceed_80,
        avg(case when RepRatio >0.5  then 1. else 0. end) as Exceed_50,
        avg(case when RepRatio >0.2  then 1. else 0. end) as Exceed_20,
        @rep_lower as rep_lower_limit,
        @rep_upper as rep_upper_limit,
        count(*) as nUser from
(
select 
     sum(case when Votes.VoteTypeId is null then 0 else 1 end) as nUp, 
     max(t1.Reputation) as Rep ,
     sum(case when Votes.VoteTypeId is null then 0. else 1. end)*10./max(t1.Reputation) as RepRatio,
     t1.Id as userId
FROM Posts

    INNER JOIN Votes ON Posts.Id = Votes.PostId 
                      and Votes.VoteTypeId = 2 
                      and Posts.PostTypeId = 1
    
    RIGHT JOIN (
      select  Id, Reputation 
      from Users

      where Reputation > @rep_lower and Reputation < @rep_upper

      )as t1 ON t1.Id = Posts.OwnerUserId 

    
group by t1.Id
    
)as tratio

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results