WITH TopTags AS ( SELECT TOP 50000 T.Id Id, T.TagName Ta...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

WITH
TopTags AS
  (
    SELECT TOP 50000 
    T.Id Id,
   
    T.TagName TagName,
    count(*) TagCount
    FROM Posts P
      INNER JOIN PostTags PT ON PT.PostId = P.Id
      INNER JOIN Tags T ON T.Id = PT.TagId
    WHERE
      P.PostTypeId = 1 -- only questions
      and  year(P.CreationDate) =2020
      AND T.TagName != 'untagged'
    GROUP BY
      T.Id,
      T.TagName
     
    ORDER BY
      TagCount DESC
      ),
  
  TagPairs AS
  (
    SELECT      
      T1.Id TagId1,
      
      T1.TagName TagName1,
     
      P.Id PostId,
      P.CreationDate CreationDate,
      P.AcceptedAnswerId AcceptedAnswerId,
      T1.TagCount Count1
      
    FROM Posts P
      INNER JOIN PostTags PT1 ON PT1.PostId = P.Id
      
      INNER JOIN TopTags T1 ON T1.Id = PT1.TagId 
      
    WHERE
      
      year(P.CreationDate) =2020
    GROUP BY
      T1.Id,
      
      T1.TagName,
      P.Id,
      P.CreationDate,
      P.AcceptedAnswerId,
      T1.TagCount
      
    ),  
    
    TagPairsNoAnswers AS
    (
      SELECT
      
      TagId1, 
       count(*) TagCount,
      TagName1,
     
      Count1,
      
      sum(case when AcceptedAnswerId is NULL then 1 else 0 end) AS NoAcceptedAnswers
      
      FROM TagPairs
      GROUP BY 
      TagId1, 
      
      TagName1,
     
      Count1
      
    ),
     TagPairsNoAnswersPercentage AS
    (
      SELECT
      *,
      (100*NoAcceptedAnswers/TagCount) PercentageNoAcceptedAnswers
      FROM TagPairsNoAnswers
    ),
      
    
    
    TagPairsWithAcceptedAnswers AS
    (
      SELECT 
      TP.TagId1,
     
      TP.TagName1,
      
      TP.PostId,
      TP.CreationDate,
      TP.AcceptedAnswerId,
      DATEDIFF(mi, TP.CreationDate, A.CreationDate) AS ResponseMinutes
      FROM TagPairs TP
      INNER JOIN Posts A ON TP.AcceptedAnswerId=A.Id      
    ),
    TagPairsMedian AS
    (      
      SELECT DISTINCT 
      TP.TagId1,
     
      TP.TagName1,
     
      PERCENTILE_CONT(0.5) 
      WITHIN GROUP (ORDER BY TP.ResponseMinutes)
      OVER (PARTITION BY TP.TagId1, TP.TagName1) AS Median
      FROM TagPairsWithAcceptedAnswers TP
    )
    
    SELECT 
    TPNAP.TagId1, 
     
      TPNAP.TagName1,
      
      TPNAP.Count1,
      
    
      TPNAP.PercentageNoAcceptedAnswers,
      TPM.Median      
    FROM TagPairsNoAnswersPercentage TPNAP
    LEFT OUTER JOIN TagPairsMedian TPM
    ON TPNAP.TagId1 = TPM.TagId1
   
    ;

Enter Parameters

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