''''Complex'''' statements to work out churn

• David Clothier

  SSC Eights!

  Points: 831

  More actions

  December 16, 2006 at 8:56 am

  #175292

  Hi. I have a need to create a report based on user *churn* on our system.

  I have a list of unique UID's for each month that I want this for - Dec 05 until now... What I need to figure out is the amount of unique users in Dec 05, then see how many of those are still active in Jan 06, Feb 06.. and so on...

  Also, all the new UID's in Jan 06, Feb 06 need to be calculated the same way for the months that follow until now...

  Does this make sense?

  Is it possible in SQL?

  Where on earth do I start?

  Thanks in advance,

  David

• DaveItz

  SSCrazy

  Points: 2146

  More actions

  December 16, 2006 at 5:50 pm

  #678535

  It's definitely possible.

  If all else fails, you can always fall back on cursors or loops.

  Here's an attempt at a set-based approach:

  declare @UserActivity table(UserID int, WorkDate datetime)
  insert into @UserActivity
  select 1, '12/01/2005 8:45 AM' union all
  select 1, '12/02/2005 5:20 PM' union all
  select 1, '01/01/2006 9:00 AM' union all
  select 1, '03/01/2006 9:02 AM' union all
  select 1, '04/01/2006 9:00 AM' union all
  select 1, '06/01/2006 8:45 AM' union all
  select 1, '10/01/2006 8:31 AM' union all
  select 1, '11/01/2006 8:52 AM' union all
  select 1, '12/01/2006 9:03 AM' union all
  select 2, '11/01/2006 9:01 AM' union all
  select 2, '12/01/2006 8:59 AM
  
  declare @StartingMonths table(UserID int, StartingMonth datetime)
  insert into @StartingMonths(UserID, StartingMonth)
  select
     ua.UserID,
     dateadd(mm, datediff(month, 0, ua.WorkDate), 0)
  from @UserActivity ua
  left join (select
                UserID,
                [Month]=dateadd(mm, datediff(month, 0, WorkDate), 0)
             from @UserActivity
             group by UserID, dateadd(mm, datediff(month, 0, WorkDate), 0)) t on ua.UserID=t.UserID
                                                                             and dateadd(mm, datediff(month, 0, ua.WorkDate)-1, 0) = t.[Month]
  where t.UserID is null
  group by ua.UserID, dateadd(mm, datediff(month, 0, ua.WorkDate), 0)
  
  select
    t.UserID,
    t.EndingMonth,
    t.StartingMonth,
    ConsecutiveMonthsWithActivity = datediff(month, t.StartingMonth, t.EndingMonth)+1
  from
  (
  select
     ua.UserID,
     [StartingMonth] = Max(sm.StartingMonth),
     [EndingMonth] = dateadd(mm, datediff(month, 0, ua.WorkDate), 0)
  from @UserActivity ua
  left join @StartingMonths sm on sm.UserID=ua.UserID
                              and sm.StartingMonth <= dateadd(mm, datediff(month, 0, ua.WorkDate), 0)
  group by ua.UserID, dateadd(mm, datediff(month, 0, ua.WorkDate), 0)
  ) t
  order by t.UserID, t.EndingMonth desc
  --order by t.EndingMonth, t.UserID