Get Actions per Day Around Badge Boundary

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

/*
  Query to find the actions of users on StackOverflow in relation to
  a specific badge boundary. The actions of interest are:
    - Edits (counting toward edit badges)
    - Questions
    - Answers
    - Comments
    - Votes on Questions (not included in this query)
    - Votes on Answers (not included in this query)
    - Review Tasks (that count toward Review badges; not included in this query)
  We'd like to run this query for the following Badges:
    - Civic Duty
    - Reviewer
    - Steward
    - Strunk & White
    - Copy Editor
    - Electorate
*/

DECLARE @EditTitle    int = 4
DECLARE @EditBody     int = 5
DECLARE @DaysSince    int = -90
DECLARE @DaysTo       int = 90
DECLARE @badgeName     nvarchar(50) = 'Strunk & White'
DECLARE @goldBadgeName nvarchar(50) = 'Copy Editor'
-- this last parameter should be '2011-01-22' for the editing badges,
-- '2012-09-21' for the reviewing badges and '2008-01-01' for the voting
-- badges if possible (I placed 2014 as the online tool timed out on this 
-- query).
DECLARE @dateOfBadge  datetime = '2014-01-01'

SELECT b.UserId, b.Name, b.Date
INTO #UsersWhoGotBadge
FROM Badges b
WHERE 
    b.Name = @badgeName AND
    -- dont want badges awarded before this change where there was 
    -- a different limit
    b.Date > @dateOfBadge;
/*
WHERE 
    b.Name = 'Copy Editor' 
    b.Date > '2011-02-22';
WHERE 
    b.Name = 'Electorate'
WHERE 
    b.Name = 'Civic Duty'
WHERE 
    b.Name = 'Reviewer'
    -- Introduction of Steward and Reviewer Badge
    b.Date > '2012-09-21'
WHERE 
    b.Name = 'Steward'
    -- Introduction of Steward and Reviewer Badge
    b.Date > '2012-09-21'
*/

create table #resultTable (
     UserId int not null,
     days_since_badge int not null,
     count_edit_action int DEFAULT 0,
     count_answer_action int DEFAULT 0,
     count_question_action int DEFAULT 0,
     count_comment_action int DEFAULT 0,
     -- note: only when running for Silver Badge and also changed to
     -- relevant Gold Badge.
     achieved_copy_editor tinyint DEFAULT 0, 
     primary key (UserId, days_since_badge)
);
 
 /*
 For the cases of the silver badge, we'd like to know which users
 then continued to achieve the gold badge in that category. We therefore
 also include a query that labels which users also achieved gold.
*/
SELECT b.UserId
INTO #UsersWhoGotGoldBadge
FROM Badges b
WHERE 
    b.Name = @goldBadgeName AND
    b.Date > @dateOfBadge;
 
/*
 Select the PostHistory that counts towards the
 editing (Strunk & White; Copy Editor) badges from 
 the users who achieved badge @BadgeName
*/

-- Get the edits that I made on my own posts
SELECT 
   ph.Id, ph.UserId
INTO 
   #EditsToMyPosts
FROM 
   PostHistory ph
INNER JOIN #UsersWhoGotBadge b
    ON (b.UserId = ph.UserId)
INNER JOIN Posts p
    ON (p.Id = ph.PostId AND p.OwnerUserId = ph.UserId);


SELECT 
    t.UserId,
    DATEDIFF(day, t.Date, t.CreationDate) as days_since_badge,
    count(t.RevisionGUID) count_edit_action
INTO #EditsQuery
FROM (
SELECT ph.UserId, ph.RevisionGUID,
       MIN(b.Date) as Date,
       MIN(ph.CreationDate) as CreationDate
  FROM PostHistory ph
  INNER JOIN #UsersWhoGotBadge b
    ON ( 
       b.UserId = ph.UserId AND
       ph.PostHistoryTypeId  in (4,5) AND
       ph.Id NOT IN (SELECT m.Id FROM #EditsToMyPosts m WHERE m.UserId = ph.UserId)
    )
   GROUP BY ph.UserId, ph.RevisionGUID
) t
GROUP BY t.UserId, DATEDIFF(day, t.Date, t.CreationDate);
  
  
/*
 Select the Answer actions from
 the users who achieved badge @BadgeName
*/
SELECT
       b.UserId,
       DATEDIFF(day, b.Date, p.CreationDate) as days_since_badge,
       count(DATEDIFF(day, b.Date, p.CreationDate)) count_answer_action
INTO
       #AnswersQuery
FROM
       Posts p
INNER JOIN #UsersWhoGotBadge b
   ON (
       b.UserId = p.OwnerUserId AND
       p.PostTypeId = 2
   )
GROUP BY b.UserId, DATEDIFF(day, b.Date, p.CreationDate);

/*
 Select the Question actions from
 the users who achieved badge @BadgeName
*/
SELECT       
       b.UserId,
       DATEDIFF(day, b.Date, p.CreationDate) as days_since_badge,
       count(DATEDIFF(day, b.Date, p.CreationDate)) count_question_action
INTO
       #QuestionQuery
FROM
       Posts p
INNER JOIN #UsersWhoGotBadge b
   ON (
       b.UserId = p.OwnerUserId AND
       p.PostTypeId = 1
   )
GROUP BY b.UserId, DATEDIFF(day, b.Date, p.CreationDate);

/*
 Select the comment actions from
 the users who achieved badge @BadgeName
*/
SELECT
       c.UserId,
       DATEDIFF(day, b.Date, c.CreationDate) as days_since_badge,
       count(DATEDIFF(day, b.Date, c.CreationDate)) count_comment_action
INTO
       #CommentQuery
FROM
       Comments c
INNER JOIN #UsersWhoGotBadge b
   ON ( b.UserId = c.UserId )
GROUP BY c.UserId, DATEDIFF(day, b.Date, c.CreationDate);


/* 
  Merge all of the results into one table
*/
MERGE #resultTable AS T
USING #EditsQuery AS U
    ON U.UserId = T.UserId AND U.days_since_badge = T.days_since_badge
WHEN MATCHED THEN 
    UPDATE SET T.count_edit_action = U.count_edit_action
WHEN NOT MATCHED THEN
    INSERT (UserId, days_since_badge, count_edit_action) 
    VALUES (U.UserId, U.days_since_badge, U.count_edit_action);
    
MERGE #resultTable AS T
USING #AnswersQuery AS U
    ON U.UserId = T.UserId AND U.days_since_badge = T.days_since_badge
WHEN MATCHED THEN 
    UPDATE SET T.count_answer_action = U.count_answer_action
WHEN NOT MATCHED THEN
    INSERT (UserId, days_since_badge, count_answer_action) 
    VALUES (U.UserId, U.days_since_badge, U.count_answer_action);
    
MERGE #resultTable AS T
USING #QuestionQuery AS U
    ON U.UserId = T.UserId AND U.days_since_badge = T.days_since_badge
WHEN MATCHED THEN 
    UPDATE SET T.count_question_action = U.count_question_action
WHEN NOT MATCHED THEN
    INSERT (UserId, days_since_badge, count_question_action) 
    VALUES (U.UserId, U.days_since_badge, U.count_question_action);
    
MERGE #resultTable AS T
USING #CommentQuery AS U
    ON U.UserId = T.UserId AND U.days_since_badge = T.days_since_badge
WHEN MATCHED THEN 
    UPDATE SET T.count_comment_action = U.count_comment_action
WHEN NOT MATCHED THEN
    INSERT (UserId, days_since_badge, count_comment_action) 
    VALUES (U.UserId, U.days_since_badge, U.count_comment_action);
    
MERGE #resultTable AS T
USING #UsersWhoGotGoldBadge AS U
    ON U.UserId = T.UserId
WHEN MATCHED THEN 
    UPDATE SET T.achieved_copy_editor = 1;

/*
   Return the results.
   We only need 90 days before and 90 days after the badge boundary.
*/
SELECT * FROM #resultTable
WHERE
  days_since_badge BETWEEN @DaysSince AND @DaysTo
ORDER BY UserId DESC, days_since_badge ASC;

Enter Parameters

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