Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry
With members as ( select u.Id as Id, min(p.CreationDate) as FirstPostDate from Users u join Posts p on u.id = p.OwnerUserId where DATEDIFF(hh, '2012/01/01 00:00', u.CreationDate) > 0 group by u.Id ), posttimes as ( select m.Id, p.Score, DATEDIFF(hh, m.FirstPostDate, p.CreationDate) as timediff from members m join Posts p on m.Id = p.OwnerUserId ), sumtimes as ( select Id, sum(case when timediff =0 then 1 else 0 end) firstHourPosts, sum(case when timediff <=23 then 1 else 0 end) firstDayPosts, sum(case when timediff <=167 then 1 else 0 end) firstWeekPosts from posttimes group by Id ) select s.firstWeekPosts, count(u.Id) as nTot, sum(case when u.Reputation < 100 then 1 else 0 end) as nLow, sum(case when u.Reputation > 2000 then 1 else 0 end) as nHigh from sumtimes s join users u on s.Id = u.Id group by s.firstWeekPosts