all users annually: year joined compared to years active

1

Please login or register to vote for this query.

(click on this box to dismiss)

another "user retention" query. This one compares join dates to actual years active (as opposed to number of years on Pivot Query #884564)

Moderators

Q&A for community managers, administrators, and moderators

select  
  p.owneruserid as userID,
  year(u.creationdate) as YearJoined,
  year(p.creationdate) as YearPosted,
  count(*) as [#Posts]
into #temp from posts p right outer join users u on p.owneruserid = u.id
where p.owneruserid >0
group by p.owneruserid, year(u.creationdate), year(p.creationdate)


select YearJoined, YearPosted, Count(userID) as [#Users]
from #temp
where userid>0
group by YearJoined, YearPosted
order by YearJoined, YearPosted

select top 100 userID, YearJoined, YearPosted, [#Posts]
from #temp
order by userid, YearJoined, YearPosted

Enter Parameters

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