0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- 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 <= 36
begin
  insert #Temp(month) values(@counter)
  set @counter = @counter + 1
end

select 
  SUM(A0) as [100+],
  SUM(A) as [200+],
  SUM(B) as [300+],
  SUM(C) as [400+],
  SUM(D) as [500+],
  SUM(E) as [600+],
  SUM(F) as [800+],
  SUM(G) as [1000+],
  SUM(H) as [2000+],
  SUM(I) as [4000+],
  SUM(J) as [6000+],
  SUM(K) as [8000+], 
  SUM(L) as [10000+],
  month as [Months Since Seen]
from
(
  select #Temp.month,
    CASE WHEN Reputation BETWEEN 100 and 199 then 1 else 0 end as A0,
    CASE WHEN Reputation BETWEEN 200 and 299 then 1 else 0 end as A,
    CASE WHEN Reputation BETWEEN 300 and 399 then 1 else 0 end as B,
    CASE WHEN Reputation BETWEEN 400 and 499 then 1 else 0 end as C,
    CASE WHEN Reputation BETWEEN 500 and 599 then 1 else 0 end as D,
    CASE WHEN Reputation BETWEEN 600 and 799 then 1 else 0 end as E,
    CASE WHEN Reputation BETWEEN 800 and 999 then 1 else 0 end as F,
    CASE WHEN Reputation BETWEEN 1000 and 1999 then 1 else 0 end as G,
    CASE WHEN Reputation BETWEEN 2000 and 3999 then 1 else 0 end as H,
    CASE WHEN Reputation BETWEEN 4000 and 5999 then 1 else 0 end as I,
    CASE WHEN Reputation BETWEEN 6000 and 7999 then 1 else 0 end as J,
    CASE WHEN Reputation BETWEEN 8000 and 9999 then 1 else 0 end as K,
    CASE WHEN Reputation >= 10000 THEN 1 else 0 end as L
  from #Temp
  left outer join Users on DATEDIFF (month,LastAccessDate,GETDATE()) = #Temp.month 
)
as sq
Group By SQ.month

fork query

edited jul 8 16

created jan 16 15 Who?