"Users who can't write good posts" vs "Users who want to write good posts"

0

(click on this box to dismiss)

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.

'Analytical': 'Visited every section of the FAQ (retired)'
*/

@DaysNewUser int = ##DaysNewUser:int?7##,
@GoodScore int = ##GoodScore:int?5##
select @BadgeCreationDate = cast(min(Date) as date) from Badges where Name in ('Informed', 'Analytical')

-- 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
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
where Name in ('Informed', 'Analytical')
group by UserId
) b
inner join #authors a on u.Id = a.Id
and u.CreationDate + @DaysNewUser < b.InformedDate

-- Calculate percentage overlap.
select
author_type as [Author type],
author_cnt as [Total users],
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:
Hold tight while we fetch your results
:records returned in :time ms:cached