votes per active day (days with posts)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Writing

Q&A for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing

select
u.id as [User Link],u.reputation,u.upvotes,u.downvotes,
up.activedays as allActiveDays,
upr.activedays as recentActiveDays,
round(cast(u.upvotes + u.downvotes as float)/up.activedays,2) as votesPerAllActiveDays
from
(select
owneruserid as id, count(distinct convert(date, creationdate)) as activedays
from posts
where (posttypeid = 1 or posttypeid = 2)
and deletiondate is null and closeddate is null
group by owneruserid) as up
join
(select
owneruserid as id, count(distinct convert(date, creationdate)) as activedays
from posts
where (posttypeid = 1 or posttypeid = 2)
and deletiondate is null and closeddate is null
and year(creationdate) >= 2016
group by owneruserid) as upr
on up.id = upr.id
join users u
on u.id = up.id
where
u.reputation > 101
and year(u.LastAccessDate) >= 2016
and upr.activedays > 10
order by up.activedays desc

Enter Parameters

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