Top Answerers, by your tags and your date range

2

Please login or register to vote for this query.

(click on this box to dismiss)

See http://stackapps.com/q/6668/7653

Stack Overflow

Q&A for professional and enthusiast programmers

-- fromDate: Rep events on, or after, Date (EG 2015-11-01) "IMPORTANT: All vote times are truncated to midnight."
-- toDate: Rep events on, or before, Date (EG 2015-11-10) "IMPORTANT: All vote times are truncated to midnight."
-- Tag1: Tag name (lowercase) (required) "Enter a tag name (lowercase)"
-- Tag2: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
-- Tag3: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
-- Tag4: Tag name (lowercase) or 'NA' "Enter a tag name or uppercase NA"
DECLARE @fromDate datetime      = ##fromDate:string?2015-10-01##;
DECLARE @toDate   datetime      = dateAdd (dd, 1, ##toDate:string?2015-11-10##);
DECLARE @Tag1     varchar(255)  = ##Tag1:string##;
DECLARE @Tag2     varchar(255)  = ##Tag2:string?NA##;
DECLARE @Tag3     varchar(255)  = ##Tag3:string?NA##;
DECLARE @Tag4     varchar(255)  = ##Tag4:string?NA##;

WITH repByUserByDateAndTag AS (
    SELECT
                a.OwnerUserId                                                       AS userID,
                SUM( CASE WHEN v.votetypeid = 1  AND  a.OwnerUserId != q.OwnerUserId
                    then 1 else 0 end
                )                                                                   AS acceptV,
                SUM( CASE WHEN v.votetypeid = 2 then 1 else 0 end )                 AS upV,
                SUM( CASE WHEN v.votetypeid = 3 then 1 else 0 end )                 AS downV,
                SUM( CASE WHEN v.votetypeid = 9 then v.BountyAmount else 0 end )    AS bounties
    FROM        Votes v
    INNER JOIN  Posts a         ON a.ID = v.PostId
    INNER JOIN  Posts q         ON q.ID = a.ParentId
    INNER JOIN  PostTags pt     ON q.ID = pt.PostId
    INNER JOIN  Tags t          ON t.ID = pt.TagId

    WHERE       v.VoteTypeId    IN (1, 2, 3, 9) -- Accept, up-vote, down-vote, award-bounty
    AND         v.CreationDate  >= @fromDate
    AND         v.CreationDate  <  @toDate  -- Dates are truncated to midnight so the edge case is critical.
    AND         a.PostTypeId    = 2 -- Answers
    AND         t.TagName       = @Tag1
    AND         (
        @Tag2   = 'NA'  OR  EXISTS (
            SELECT      pt2.PostId
            FROM        PostTags        pt2
            INNER JOIN  Tags            t2
            ON          t2.Id           = pt2.TagId
            WHERE       pt2.PostId      = q.Id
            AND         t2.TagName      = @Tag2
        )
    )
    AND         (
        @Tag3   = 'NA'  OR  EXISTS (
            SELECT      pt2.PostId
            FROM        PostTags        pt2
            INNER JOIN  Tags            t2
            ON          t2.Id           = pt2.TagId
            WHERE       pt2.PostId      = q.Id
            AND         t2.TagName      = @Tag3
        )
    )
    AND         (
        @Tag4   = 'NA'  OR  EXISTS (
            SELECT      pt2.PostId
            FROM        PostTags        pt2
            INNER JOIN  Tags            t2
            ON          t2.Id           = pt2.TagId
            WHERE       pt2.PostId      = q.Id
            AND         t2.TagName      = @Tag4
        )
    )
    GROUP BY    a.OwnerUserId
)
SELECT
            COALESCE (r.userID, 0)                                          AS [User Link],
            r.upV - r.downV                                                 AS Score,
            10 * r.upV  -  2 * r.downV  +  15 * r.acceptV  + r.bounties     AS [Uncapped Rep],
            r.upV                                                           AS [Up votes],
            r.downV                                                         AS [Dwn votes],
            r.acceptV                                                       AS [Accepted Ans],
            r.bounties                                                      AS [Bounties]
FROM        repByUserByDateAndTag r
LEFT JOIN   Users u     ON u.ID = r.userID
ORDER BY    Score  DESC,
            [Uncapped Rep] DESC,
            u.DisplayName

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results