Action Count per Day (Columns) per User (Rows) around Badge

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

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'

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;
    

-- ACTION SPECIFIC
-- 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_action
INTO #ActionQuery
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 *
from (
  select UserId, days_since_badge, count_action
  from #ActionQuery
) src
pivot
(
  SUM(count_action) for days_since_badge in (
  [-45], [-44], [-43], [-42], [-41], [-40], [-39], [-38], [-37], [-36], [-35], [-34], [-33], [-32], [-31], [-30], [-29], [-28], [-27], [-26], [-25], [-24], [-23], [-22], [-21], [-20], [-19], [-18], [-17], [-16], [-15], [-14], [-13], [-12], [-11], [-10], [-9], [-8], [-7], [-6], [-5], [-4], [-3], [-2], [-1], [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45]
  )
) piv;

Enter Parameters

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