SET NOCOUNT ON DECLARE @MonthsOfData AS INT = 1 DECLARE ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SET NOCOUNT ON

DECLARE @MonthsOfData AS INT = 1
DECLARE @BadgeToCompare AS VARCHAR(20) = 'Yearling'

DECLARE @Top50Tags      AS TABLE
    ( 
    TagId         INT
    )
    
INSERT INTO
    @Top50Tags
    
SELECT TOP 50
    PostTags.TagId

FROM
    PostTags
    
GROUP BY
    PostTags.TagId
    
ORDER BY 
    COUNT( * )       DESC

    
    
    
DECLARE @BadgeAnswers AS TABLE
    ( 
    UserId         INT,
    AnswerId       INT,
    QuestionId     INT,
    TopTag         INT
    )
    
INSERT INTO
    @BadgeAnswers
    
SELECT
    Answers.OwnerUserId            AS UserId,
    Answers.Id                     AS AnswerId,
    Questions.Id                   AS QuestionId,
    CASE WHEN Top50Tags.TagId IS NULL
    THEN 0
    ELSE 1
    END                            AS TopTag
    
FROM
    Badges
    
    INNER JOIN
    Posts                          AS Answers
    ON Badges.UserId               = Answers.OwnerUserId
    
    INNER JOIN
    Posts                          AS Questions
    ON Answers.ParentId            = Questions.Id
    
    INNER JOIN
    PostTags                       AS PostTags
    ON Questions.Id                = PostTags.PostId
    
    LEFT JOIN
    @Top50Tags                  AS Top50Tags
    ON PostTags.TagId           = Top50Tags.TagId

WHERE
        Badges.Name                = 'Unsung Hero'
    AND Answers.PostTypeId         = 2 --answers
    AND Answers.Score              = 0
    AND Questions.PostTypeId       = 1 --questions
    AND Questions.AcceptedAnswerId = Answers.Id
    AND Badges.Date                > DATEADD( MONTH, -6, GETDATE() )
    AND Answers.CreationDate       < Badges.Date --only answers before badge


DECLARE @Answers INT
DECLARE @Users   INT

SELECT 
    @Users   = COUNT( DISTINCT UserId ), 
    @Answers = COUNT( DISTINCT AnswerId )
    
FROM 
    @BadgeAnswers
  


DECLARE @AcceptedPercent AS DECIMAL( 12,2 )

SELECT
    @AcceptedPercent = ROUND( AVG( AcceptedPercentage.Accepted ), 2 ) 

FROM
    ( 
    SELECT
        ROUND(
            SUM( 
                CASE Questions.AcceptedAnswerId 
                WHEN Answers.Id THEN 1 
                ELSE 0 END
            ) 
            * 100.0 / COUNT( Answers.Id )
        , 1)                            AS [Accepted]
        
    FROM    
        @BadgeAnswers                   AS BadgeAnswers
        
        INNER JOIN
        Posts                           AS Answers
        ON BadgeAnswers.UserId        = Answers.OwnerUserId
        
        INNER JOIN    
        Posts                           AS Questions
        ON Answers.ParentId             = Questions.Id
        
    WHERE
            Questions.PostTypeId        = 1
        AND Answers.PostTypeId          = 2
        AND Questions.AcceptedAnswerId  IS NOT NULL
        
    GROUP BY
        Answers.OwnerUserId
        
    ) AS AcceptedPercentage
    
    
    
SELECT 
    @Users                                 AS [Unsung Heroes],
    @AcceptedPercent                       AS [% Accepted Answer],
    --@Answers                               AS [0-Score Accepted Answers],
    --COUNT( DISTINCT AnswerId )             AS [With Top 50 Tag],
    CAST( COUNT( DISTINCT AnswerId )
    / CAST( @Answers AS DECIMAL (12,2)) AS DECIMAL(12, 2) )  AS [% With Top 50 Tag]
    
FROM 
    @BadgeAnswers               AS BadgeAnswers
    
    INNER JOIN
    PostTags                    AS PostTags
    ON BadgeAnswers.QuestionId  = PostTags.PostId
    
    INNER JOIN
    @Top50Tags                  AS Top50Tags
    ON PostTags.TagId           = Top50Tags.TagId;
    



DECLARE @NoBadgeAnswers AS TABLE
    ( 
    UserId         INT,
    AnswerId       INT,
    QuestionId     INT,
    TopTag         INT
    )
    
INSERT INTO
    @NoBadgeAnswers
    
SELECT
    Answers.OwnerUserId            AS UserId,
    Answers.Id                     AS AnswerId,
    Questions.Id                   AS QuestionId,
    CASE WHEN Top50Tags.TagId IS NULL
    THEN 0
    ELSE 1
    END                            AS TopTag
    
FROM
    Badges
    
    INNER JOIN
    Posts                          AS Answers
    ON Badges.UserId               = Answers.OwnerUserId
    
    INNER JOIN
    Posts                          AS Questions
    ON Answers.ParentId            = Questions.Id
    
    INNER JOIN
    PostTags                       AS PostTags
    ON Questions.Id                = PostTags.PostId
    
    LEFT JOIN
    @Top50Tags                  AS Top50Tags
    ON PostTags.TagId           = Top50Tags.TagId

WHERE
        Badges.Name                = @BadgeToCompare
    AND Answers.PostTypeId         = 2 --answers
    AND Answers.Score              = 0
    AND Questions.PostTypeId       = 1 --questions
    AND Questions.AcceptedAnswerId = Answers.Id
    AND Badges.Date                > DATEADD( MONTH, -@MonthsOfData, GETDATE() )
    AND Answers.CreationDate       < Badges.Date --only answers before badge
        


SELECT 
    @Users   = COUNT( DISTINCT UserId ), 
    @Answers = COUNT( DISTINCT AnswerId )
    
FROM 
    @NoBadgeAnswers
  
  
  

SELECT
    @AcceptedPercent = ROUND( AVG( AcceptedPercentage.Accepted ), 2 ) 

FROM
    ( 
    SELECT --TOP 5000
        ROUND(
            SUM( 
                CASE Questions.AcceptedAnswerId 
                WHEN Answers.Id THEN 1 
                ELSE 0 END
            ) 
            * 100.0 / COUNT( Answers.Id )
        , 1)                            AS [Accepted]
        
        
    FROM    
        ( SELECT DISTINCT TOP 1000 UserId FROM @NoBadgeAnswers ) AS NoBadgeAnswers
        
        INNER JOIN
        Posts                           AS Answers
        ON NoBadgeAnswers.UserId        = Answers.OwnerUserId
        
        INNER JOIN    
        Posts                           AS Questions
        ON Answers.ParentId             = Questions.Id
        
    WHERE
            Questions.PostTypeId        = 1
        AND Answers.PostTypeId          = 2
        AND Questions.AcceptedAnswerId  IS NOT NULL
        
    GROUP BY
        Answers.OwnerUserId
        
    ) AS AcceptedPercentage
    
    
    

SELECT 
    @Users                                 AS [Everyone],
    @AcceptedPercent                       AS [% Accepted Answer],
    --@Answers                               AS [0-Score Accepted Answers],
    --COUNT( DISTINCT AnswerId )             AS [With Top 50 Tag],
    CAST( COUNT( DISTINCT AnswerId )
    / CAST( @Answers AS DECIMAL (12,2)) AS DECIMAL(12, 2) )  AS [% With Top 50 Tag]
    
FROM 
    @NoBadgeAnswers               AS NoBadgeAnswers
    
    INNER JOIN
    PostTags                    AS PostTags
    ON NoBadgeAnswers.QuestionId  = PostTags.PostId
    
    INNER JOIN
    @Top50Tags                  AS Top50Tags
    ON PostTags.TagId           = Top50Tags.TagId;

Enter Parameters

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