Медиана репутации активных пользователей

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском

Вопросы и ответы для программистов

declare @total bigint, @median bigint

declare @mpn table (MinPostsNumber bigint)
insert into @mpn values (1)
insert into @mpn select (select max(MinPostsNumber) from @mpn) + MinPostsNumber from @mpn
insert into @mpn select (select max(MinPostsNumber) from @mpn) + MinPostsNumber from @mpn
insert into @mpn select (select max(MinPostsNumber) from @mpn) + MinPostsNumber from @mpn
insert into @mpn select (select max(MinPostsNumber) from @mpn) + MinPostsNumber from @mpn
insert into @mpn select (select max(MinPostsNumber) from @mpn) + MinPostsNumber from @mpn
insert into @mpn select (select max(MinPostsNumber) from @mpn) + MinPostsNumber from @mpn

select
  MinPostsNumber,
  ( select distinct PERCENTILE_DISC(0.5) within group (order by Reputation) over (partition by 1) AS MedianReputation
    from Users u
    inner join Posts p on p.OwnerUserId = u.Id
    inner join PostTypes t on p.PostTypeId = t.Id and t.Name = N'Answer'
    where u.LastAccessDate > DATEADD(m, -1, GETDATE())
    group by u.Id, u.Reputation
    having count(distinct p.Id) >= MinPostsNumber and MIN(p.CreationDate) < DATEADD(m, -1, GETDATE())
  ) as Median,
  ( select distinct PERCENTILE_DISC(0.5) within group (order by Reputation) over (partition by 1) AS MedianReputation
    from Users u
    inner join Posts p on p.OwnerUserId = u.Id
    inner join PostTypes t on p.PostTypeId = t.Id and t.Name = N'Answer'
    where u.Reputation > 50 and u.LastAccessDate > DATEADD(m, -1, GETDATE())
    group by u.Id, u.Reputation
    having count(distinct p.Id) >= MinPostsNumber and MIN(p.CreationDate) < DATEADD(m, -1, GETDATE())
  ) as Median50
from @mpn
order by MinPostsNumber

Enter Parameters

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