Q&A for committed Christians, experts in Christianity and those interested in learning more
DECLARE @ViewCountMin float, @ViewCountMax float; DECLARE @AnswerCountMin float, @AnswerCountMax float; DECLARE @QCommentCountMin float, @QCommentCountMax float; DECLARE @ACommentCountMin float, @ACommentCountMax float; DECLARE @FavoriteCountMin float, @FavoriteCountMax float; DECLARE @QScoreMin float, @QScoreMax float; DECLARE @AScoreMin float, @AScoreMax float; DECLARE @CScoreMin float, @CScoreMax float; SELECT @ViewCountMin = MIN(cast(ViewCount as float)), @ViewCountMax = MAX(cast(ViewCount as float)), @AnswerCountMin = MIN(cast(AnswerCount as float)), @AnswerCountMax = MAX(cast(AnswerCount as float)), @QCommentCountMin = MIN(cast(CommentCount as float)), @QCommentCountMax = MAX(cast(CommentCount as float)), @FavoriteCountMin = MIN(cast(FavoriteCount as float)), @FavoriteCountMax = MAX(cast(FavoriteCount as float)), @QScoreMin = MIN(cast(Score as float)), @QScoreMax = MAX(cast(Score as float)) FROM Posts WHERE PostTypeId = 1 and CreationDate between '2013-01-01' and '2014-01-01'; SELECT @AScoreMin = MIN(cast(Score as float)), @AScoreMax = MAX(cast(Score as float)) , @ACommentCountMin = MIN(cast(CommentCount as float)), @ACommentCountMax = MAX(cast(CommentCount as float)) FROM Posts WHERE PostTypeId = 2 and CreationDate between '2013-01-01' and '2014-01-01'; SELECT @CScoreMin = MIN(cast(Score as float)), @CScoreMax = MAX(cast(Score as float)) FROM Comments WHERE CreationDate between '2013-01-01' and '2014-01-01'; select distinct AcceptedAnswerId into #bestAnswers from Posts where PostTypeId in (1) and OwnerUserId is not null and CreationDate between '2013-01-01' and '2014-01-01'; SELECT Id, CASE PostTypeId WHEN 1 THEN Id ELSE ParentId END as QuestionId, CASE PostTypeId WHEN 1 THEN null ELSE Id END as AnswerId, OwnerUserId, PostTypeId, CreationDate, CASE PostTypeId WHEN 1 THEN 1*(log(ViewCount) - log(@ViewCountMin))/(log(@ViewCountMax) - log(@ViewCountMin)) + 1*(log(1+FavoriteCount) - log(1+@FavoriteCountMin))/(log(1+@FavoriteCountMax) - log(1+@FavoriteCountMin)) + 1*(log(1+AnswerCount) - log(1+@AnswerCountMin))/(log(1+@AnswerCountMax) - log(1+@AnswerCountMin)) + CASE WHEN Score < 0 THEN -1*(log(1+abs(Score)) - 0)/(log(1+abs(@QScoreMin)) - 0) WHEN Score > 0 THEN 1*(log(1+abs(Score)) - 0)/(log(1+@QScoreMax) - 0) WHEN Score = 0 THEN 0 END + CASE WHEN AcceptedAnswerId is null THEN 0 ELSE 0.5 END WHEN 2 THEN CASE WHEN Score < 0 THEN -1*(log(1+abs(Score)) - 0)/(log(1+abs(@AScoreMin)) - 0) WHEN Score > 0 THEN 1*(log(1+abs(Score)) - 0)/(log(1+@AScoreMax) - 0) WHEN Score = 0 THEN null END + CASE WHEN Id in (select * from #bestAnswers) then 0.5 ELSE 0 end END * 1.0 as Quality INTO #tmpPosts FROM Posts WHERE PostTypeId in (1,2) and OwnerUserId is not null and CreationDate between '2013-01-01' and '2014-01-01'; SELECT Comments.Id, CASE Posts.PostTypeId WHEN 1 THEN PostId ELSE ParentId END as QuestionId, CASE Posts.PostTypeId WHEN 1 THEN null ELSE PostId END as AnswerId, Comments.UserId as OwnerUserId, 3 as PostTypeId, Comments.CreationDate, (CASE WHEN Comments.Score > 0 THEN 1*(log(1+abs(Comments.Score)) - 0)/(log(1+@CScoreMax) - 0) WHEN Comments.Score is null THEN null END) * 0.1 as Quality INTO #tmpComments FROM Comments LEFT JOIN Posts on Comments.PostId = Posts.Id WHERE UserId is not null and Posts.CreationDate between '2013-01-01' and '2014-01-01' and Comments.CreationDate between '2013-01-01' and '2014-01-01'; SELECT Quality, OwnerUserId, QuestionId, CreationDate, CASE PostTypeId WHEN 2 THEN SUM(abs(Quality)) OVER ( PARTITION BY QuestionId ORDER BY CreationDate ROWS UNBOUNDED PRECEDING ) END as KnowledgeReceived INTO #tmp FROM (select * from #tmpPosts union all select * from #tmpComments) as sub SELECT OwnerUserId, TagId, SUM(KnowledgeReceived) OVER (PARTITION BY OwnerUserId,TagId ORDER BY CreationDate ROWS UNBOUNDED PRECEDING ) AS CumulativeSum, AVG(Quality) OVER (PARTITION BY OwnerUserId,TagId ORDER BY CreationDate ROWS UNBOUNDED PRECEDING) AS CumulativeAverage, DATEDIFF(day, MIN(CreationDate) OVER (PARTITION BY OwnerUserId,TagId ORDER BY CreationDate ROWS UNBOUNDED PRECEDING), CreationDate) AS duration into #tmpResults from #tmp inner join PostTags on PostTags.PostId = QuestionId where TagId in (select top 100 Id from Tags order by Count desc) order by OwnerUserId, TagId; select round(2*CumulativeSum/(duration+1.0), 2)/2 as intensity, avg(CumulativeAverage) as avg, count(*)/1000000.0 as reliability from #tmpResults where round(2*CumulativeSum/(duration+1.0), 2)/2 <= 0.2 group by round(2*CumulativeSum/(duration+1.0), 2)/2 order by round(2*CumulativeSum/(duration+1.0), 2)/2