number of active users per month

0

Please login or register to vote for this query.

(click on this box to dismiss)

スタック・オーバーフローMeta

プログラマーと熱狂的なプログラマーのためのサイトに関する質問と回答

select Months, 
    (case when n_posts > 10 then 10
          when n_posts > 1 then 1 
          else 0 end),
    count(n_posts) from
(
select Count(Id) as n_posts ,
       1.000*datepart(year,CreationDate)+
         1.000*datepart(month,CreationDate)/12 -
         1.000/24 as Months, 
       OwnerUserId 
from Posts
where PostTypeId = 2 and
      (1.000*datepart(year,CreationDate)+1.000*datepart(month,CreationDate)/12 < 
      1.000*datepart(year, GETDATE())+1.000*datepart(month, GETDATE())/12)
Group By OwnerUserId, 
1.000*datepart(year,CreationDate)+1.000*datepart(month,CreationDate)/12 - 1.000/24
) a
group by Months,(case when n_posts > 10then 10
          when n_posts > 1 then 1
          else 0 end)
order by (case when n_posts > 10then 10
          when n_posts > 1 then 1
          else 0 end),Months

Enter Parameters

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