/* Select all users and their cumulative post score withi...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/*
  Select all users and their cumulative post score within the first week of 
  creating their account (@DaysNewUser). 
  
  Then split these Users into bad (score <= @BadScore) and good (score >= 
  @GoodScore) and calculate the percentage who also received an 'Informed' 
  or 'Analytical' badge within this time period.
  
  'Informed': 'Read the entire about page'
  'Analytical': 'Visited every section of the FAQ (retired)'
*/

declare @BadgeCreationDate datetime = '2011-08-24',
        @DaysNewUser int = ##DaysNewUser:int?7##,
        @BadScore int = ##BadScore:int?0##,
        @GoodScore int = ##GoodScore:int?5##

-- New user scores.
select 
  p.OwnerUserId as Id, 
  sum(p.Score) as score, 
  count(p.OwnerUserId) as posts, 
  min(p.CreationDate) as PostDate
into #authors
from Posts p
inner join Users u on u.Id = p.OwnerUserId
where u.CreationDate > @BadgeCreationDate
  and u.CreationDate + @DaysNewUser < p.CreationDate
group by OwnerUserId

-- Informed new users.
select u.Id, b.InformedDate, a.Score, a.PostDate
into #informed
from (
  select UserId, min(Date) as InformedDate
  from Badges 
  where Name in ('Informed', 'Analytical')
 group by UserId
) b
inner join Users u on b.UserId = u.Id
inner join #authors a on u.Id = a.Id 
where u.CreationDate > @BadgeCreationDate
  and u.CreationDate + @DaysNewUser < b.InformedDate

-- Calculate percentage overlap.
select
  author_type as [Author type], 
  author_cnt as [Total users], 
  informed_cnt as [..who read the About/FAQ page], 
  str(cast(informed_cnt as float)*100/cast(author_cnt as float), 5, 2) 
    as [Percentage],
  first_c as [Got informed before first post]
from (
  select
    'Bad (score <= '  + cast(@BadScore as varchar(5)) + ')' as author_type,
    (select count(*) from #authors where Score <= @BadScore) as author_cnt,
    count(*) as informed_cnt, 
    count(case when InformedDate < PostDate then 1 else null end) 
      as first_c
  from #informed where Score <= @BadScore
  union all
  select 
    'Good (score >= '  + cast(@GoodScore as varchar(5)) + ')' as author_type,
    (select count(*) from #authors where Score >= @GoodScore) as author_cnt,
    count(*) as informed_cnt, 
    count(case when InformedDate < PostDate then 1 else null end) as first_c
  from #informed where Score >= @GoodScore
  union all
  select 
    'All' as author_type,
    (select count(*) from #authors) as author_cnt,
    count(*) as informed_cnt, 
    count(case when InformedDate < PostDate then 1 else null end) as first_c
  from #informed 
) p

Enter Parameters

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