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

Q&A for professional and enthusiast programmers

;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
)
, allusers as
(
select rank() over (order by count(*) desc) [rank]
     , c.userid
     , u.displayname
     , 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
inner join users u on u.id = c.userid
where c.userid is not null
group by c.userid,   u.displayname
)

select rank
     , 'site://users/'+ cast(userid as nvarchar) + '|' +  displayname [User]
     , [Number of comments]
     , [Comments per day]
     , [Comments per month]
     , [Comments per year]
     , [Total score]
     , [Avg score]
from allusers 
where userid = ##userid##
union all
select top 100 
       rank
     , 'site://users/'+ cast(userid as nvarchar) + '|' +  displayname [User]
     , [Number of comments]
     , [Comments per day]
     , [Comments per month]
     , [Comments per year]
     , [Total score]
     , [Avg score]
from allusers

fork query

edited may 21 18

created may 21 18 rene