SO Badges Work: Count Edits Per Day For Strunk and White Users

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

DECLARE 
    @columns    NVARCHAR(MAX) = '', 
    @sql        NVARCHAR(MAX) = '',
    @badge      NVARCHAR(MAX) = 'Strunk & White',
    @startDate  datetime = '2019-01-01',
    @endDate    datetime = '2020-03-01';


SELECT UserId, CAST(Date AS DATE) as Date
INTO #successfulUsers
FROM Badges
WHERE (Name = @badge AND date > @startDate AND date < @endDate AND 
        UserId NOT IN (
    SELECT UserId FROM Badges b2 WHERE b2.Name = 'Copy Editor'));

SELECT 
        ph.Id as Id, 
        CAST(ph.CreationDate AS DATE) as date, 
        ph.UserId as UserId
INTO #edits
FROM PostHistory ph
INNER JOIN #successfulUsers b
  ON (ph.UserId = b.UserId AND ph.PostHistoryTypeId  in (4,5))
INNER JOIN Posts p 
  ON (p.Id = ph.PostId AND p.OwnerUserId != ph.UserId)
WHERE ph.CreationDate > @startDate AND ph.CreationDate < @endDate;

-- select the category names
SELECT 
    @columns+=QUOTENAME(date) + ','
FROM 
    #edits
GROUP BY date
ORDER BY date;

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
SELECT *
FROM #edits
PIVOT(
    COUNT(Id) FOR date IN ('+ @columns +')
) AS pivot_table;';

-- execute the dynamic SQL
-- EXECUTE sp_executesql @sql;
SELECT UserId, Date FROM #successfulUsers;

Enter Parameters

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