Can we see a user's comment statistics, like average upvotes?

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/questions/297737/can-we-see-a-users-comment-statistics-like-average-upvotes

Quantitative Finance

Q&A for finance professionals and academics

;with daystat as
(
select userid
     , avg(cnt) [value]
from (
select userid
     , cast(creationDate as date) as grp
     , count(*) as cnt
from comments
where userid is not null
group by userid, cast(creationDate as date)
)as d
group by userid
)
, monthstat as
(
select userid
     , avg(cnt) value
from (
select userid
     , eomonth(creationDate) as grp
     , count(*) as cnt
from comments
where userid is not null
group by userid, eomonth(creationDate)
)as d
group by userid
)
, yearstat as
(
select userid
     , avg(cnt) value
from (
select userid
     , year(creationDate) as grp
     , count(*) as cnt
from comments
where userid is not null
group by userid, year(creationDate)
)as d
group by userid
)

select rank() over (order by count(*) desc) [rank]
     , c.userid
     , count(*) as [Number of comments]
     , min(ds.value) as [Comments per day]
     , min(ms.value) as [Comments per month]
     , min(ys.value) as [Comments per year]
     , sum(c.score) as [Total score]
     , avg(cast(c.score as decimal)) as [Avg score]
from comments c
inner join daystat ds on ds.userid = c.userid
inner join monthstat ms on ms.userid = c.userid
inner join yearstat ys on ys.userid = c.userid
where c.userid is not null
group by c.userid 
order by count(*) desc

Enter Parameters

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