Comments per Day by User

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

select 
  c.userid,
  u.displayname as [User Name],
  u.reputation as [Reputation],
  format(min(c.creationdate),'MMM d, yyyy') as [1st Comment],
  format(max(c.creationdate),'MMM d, yyyy') as [Last Comment],
  count(c.id) as [# Comments],
  datediff(d,min(c.creationdate),max(c.creationdate)) as [# Days],
  
  case when datediff(d,min(c.creationdate),max(c.creationdate)) = 0
       then null
       else format((cast(count(c.id) as decimal) 
       / cast(datediff(d,min(c.creationdate),max(c.creationdate)) as decimal) ),'0.0')
    end
    as [Comments/Day]
  
from
  comments c join users u
  on u.id = c.userid
  
where 
  c.userid is not null
  and year(c.creationdate)>=2024

group by 
  c.userid,
  u.reputation,
  u.displayname,
  u.creationdate

having 
  datediff(d,min(c.creationdate),max(c.creationdate)) > 30
--limited to >30 days to eliminated new users with 100 comments on 1 question (lol)
  and case when datediff(d,min(c.creationdate),max(c.creationdate)) = 0
        then null
        else format((cast(count(c.id) as decimal) 
        / cast(datediff(d,min(c.creationdate),max(c.creationdate)) as decimal) ),'0.0')
        end >= cast(0.5 as decimal)
--limit to users with at least 0.5 comment/day to keep under 50,000 rows
order by
  ( count(c.id) / datediff(d,min(c.creationdate),max(c.creationdate)) ) desc

Enter Parameters

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