DECLARE @ViewCountMin float, @ViewCountMax float; DECLARE...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Christianity

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results