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

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,
      T2.Id TagId2,
      T1.TagName TagName1,
      T2.TagName TagName2,
      P.Id PostId,
      P.CreationDate CreationDate,
      P.AcceptedAnswerId AcceptedAnswerId,
      T1.TagCount Count1,
      T2.TagCount Count2      
    FROM Posts P
      INNER JOIN PostTags PT1 ON PT1.PostId = P.Id
      INNER JOIN PostTags PT2 ON PT2.PostId = P.Id 
      INNER JOIN TopTags T1 ON T1.Id = PT1.TagId 
      INNER JOIN TopTags T2 ON T2.Id = PT2.TagId
    WHERE
      T1.TagName < T2.TagName and  
      year(P.CreationDate) =2020
    GROUP BY
      T1.Id,
      T2.Id,
      T1.TagName,
      T2.TagName,
      P.Id,
      P.CreationDate,
      P.AcceptedAnswerId,
      T1.TagCount,
      T2.TagCount  
    ),  
    
    TagPairsNoAnswers AS
    (
      SELECT
      TagId1, 
      TagId2,
      TagName1,
      TagName2,
      Count1,
      Count2,
      sum(case when AcceptedAnswerId is NULL then 1 else 0 end) AS NoAcceptedAnswers,
      count(*) AS Count12
      FROM TagPairs
      GROUP BY 
      TagId1, 
      TagId2,
      TagName1,
      TagName2,
      Count1,
      Count2
       
    
    ),
     TagPairsNoAnswersPercentage AS
    (
      SELECT
      *,
      (100*NoAcceptedAnswers/Count12) PercentageNoAcceptedAnswers
      FROM TagPairsNoAnswers
    ),
      
    
    
    TagPairsWithAcceptedAnswers AS
    (
      SELECT 
      TP.TagId1,
      TP.TagId2,
      TP.TagName1,
      TP.TagName2,
      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.TagId2,
      TP.TagName1,
      TP.TagName2,
      PERCENTILE_CONT(0.5) 
      WITHIN GROUP (ORDER BY TP.ResponseMinutes)
      OVER (PARTITION BY TP.TagId1, TP.TagId2, TP.TagName1, TP.TagName2) AS Median
      FROM TagPairsWithAcceptedAnswers TP
    )
    
    SELECT 
    TPNAP.TagId1, 
      TPNAP.TagId2,
      TPNAP.TagName1,
      TPNAP.TagName2,
      TPNAP.Count1,
      TPNAP.Count2,
      TPNAP.Count12,
      TPNAP.PercentageNoAcceptedAnswers,
      TPM.Median      
    FROM TagPairsNoAnswersPercentage TPNAP
   
    LEFT OUTER JOIN TagPairsMedian TPM
    ON TPNAP.TagId1 = TPM.TagId1
    AND TPNAP.TagId2 = TPM.TagId2
     where 
   TPNAP.Count12 >=30
     
    ;

Enter Parameters

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