0

Please login or register to vote for this query.

(click on this box to dismiss)

Non-returning users per month as a function of reputation.

Q&A for administrators, end users, developers and designers for Craft CMS

-- User drop-off with reputation
-- Non-returning users per month as a function of reputation.

create table #Temp (month int)
declare @counter int
set @counter = 0
while @counter <= 24
begin
  insert #Temp(month) values(@counter)
  set @counter = @counter + 1
end

select 
  SUM(N) as [3000->],
  SUM(A) as [4000->],
  SUM(B) as [5000->],
  SUM(C) as [6000->],
  SUM(D) as [8000->],
  SUM(E) as [10000->],
  SUM(F) as [20000->],
  SUM(G) as [50000+],
  month as [Months Since Seen]
from
(
  select #Temp.month, 
    CASE WHEN Reputation BETWEEN 3000 and 3999 then 1 else 0 end as N,
    CASE WHEN Reputation BETWEEN 4000 and 4999 then 1 else 0 end as A,
    CASE WHEN Reputation BETWEEN 5000 and 5999 then 1 else 0 end as B,
    CASE WHEN Reputation BETWEEN 6000 and 7999 then 1 else 0 end as C,
    CASE WHEN Reputation BETWEEN 8000 and 9999 then 1 else 0 end as D,
    CASE WHEN Reputation BETWEEN 10000 and 19999 then 1 else 0 end as E,
    CASE WHEN Reputation BETWEEN 20000 and 49999 then 1 else 0 end as F,
    CASE WHEN Reputation >= 50000 THEN 1 else 0 end as G
  from #Temp
  left outer join Users on DATEDIFF (month,LastAccessDate,GETDATE()) = #Temp.month 
)
as sq
Group By SQ.month

fork query

created sep 13 10 Benjol