Cumulative Historical User Count (Monthly)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Run query and then click the GRAPH tab, or link to graph directly: https://data.stackexchange.com/stackoverflow/query/870668#graph

Russian Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Russian language

select 
  eomonth(creationdate) as [Month], 
  sum(count(id)) over (order by eomonth(creationdate)) as [Total#Users]
into #usersMonthly from users
group by eomonth(creationdate)

select 
  eomonth(creationdate) as [Month], 
  sum(count(id)) over (order by eomonth(creationdate)) as [Total#Posts],
  sum(sum(score)) over (order by eomonth(creationdate)) as [TotalScore], 
  round(1.0*sum(count(id)) over (order by eomonth(creationdate))/sum(count(id)) over (order by eomonth(creationdate)),1) as [Posts/User], 
  round(1.0*sum(sum(score)) over (order by eomonth(creationdate))/sum(count(id)) over (order by eomonth(creationdate)),1) as AvgCumScore, --*cumulative* average score
  avg(score) as [avgScores]
into #postsMonthly from posts
group by eomonth(creationdate)

select
  u.[Month] as [Month],
  [Total#Users],
  [Total#Posts],
  [Posts/User],
  AvgCumScore --*cumulative* average score
from #usersMonthly p join #postsMonthly u on p.[Month] = u.[Month]
--group by u.[Month],[Total#Users],[Total#Posts]
order by u.[Month]

Enter Parameters

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