Get Edit History for People who Achieved Badge within 45 days

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ukrainian Language Meta

Q&A about the site for linguists, teachers and students of the Ukrainian language

DECLARE @DaysSince    int = -90
DECLARE @DaysTo       int = 90
DECLARE @badgeName     nvarchar(50) = 'Strunk & White'
DECLARE @dateOfBadge  datetime = '2011-01-22'

SELECT b.UserId, b.Name, b.Date
INTO #UsersWhoGotBadge
FROM Badges b
WHERE 
    b.Name = @badgeName AND
    b.Date > @dateOfBadge;


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)
    WHERE ( p.PostTypeId in (1,2) );


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 (1,2,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 
    t.UserId,
    DATEDIFF(day, t.Date, t.CreationDate) as days_since_badge,
    count(t.RevisionGUID) count_rejected_action
INTO #SuggestedEditsQuery
FROM (
SELECT se.OwnerUserId as UserId,
       se.RevisionGUID,
       MIN(b.Date) as Date,
       MIN(se.CreationDate) as CreationDate
  FROM SuggestedEdits se
  INNER JOIN #UsersWhoGotBadge b
    ON ( 
       b.UserId = se.OwnerUserId AND
       ISNULL(se.Tags, '')='' AND
       NOT ISNULL(se.RejectionDate, '')=''
    )
   GROUP BY se.OwnerUserId, se.RevisionGUID
) t
GROUP BY t.UserId, DATEDIFF(day, t.Date, t.CreationDate); 


create table #resultTable (
     UserId int not null,
     days_since_badge int not null,
     count_edit_action int DEFAULT 0,
     count_rejected_action int DEFAULT 0,
     primary key (UserId, days_since_badge)
);

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 #SuggestedEditsQuery AS U
    ON U.UserId = T.UserId AND U.days_since_badge = T.days_since_badge
WHEN MATCHED THEN 
    UPDATE SET T.count_rejected_action = U.count_rejected_action
WHEN NOT MATCHED THEN
    INSERT (UserId, days_since_badge, count_rejected_action) 
    VALUES (U.UserId, U.days_since_badge, U.count_rejected_action);


SELECT *, SUM(count_edit_action) OVER 
          (PARTITION BY UserId ORDER BY UserId, days_since_badge) as CumSum
INTO #EditsWithThresh
FROM #resultTable;

SELECT *
INTO #EditsWithDayThresh
FROM #EditsWithThresh
WHERE
  days_since_badge BETWEEN -45 AND 45;
  
SELECT UserId
INTO #UsersOfInterest
FROM #EditsWithDayThresh
GROUP BY UserId
HAVING MAX(CumSum) <= 100 AND MIN(CumSum) <= 20 AND MAX(CumSum) >= 70;

SELECT * FROM #EditsWithDayThresh
WHERE UserId IN (SELECT UserId FROM #UsersOfInterest);

Enter Parameters

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