Tag Removal Stats (With CreationDate Filter)

1

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

declare @TagName nvarchar(35) = ##TagName:string##;
declare @DaysOffset datetime = DATEADD(day, -##days:int##, GETDATE());

SELECT
    NumberOfRemovals,
    TotalInitialPosts,
    FORMAT(CAST(NumberOfRemovals AS DECIMAL(10, 0)) / CAST(TotalInitialPosts AS DECIMAL(10, 0)),
           'P3') AS PercentRemoved,
    (TotalInitialPosts - NumberOfRemovals) AS TotalRetained,
    FORMAT(CAST((TotalInitialPosts - NumberOfRemovals) AS DECIMAL(10, 0)) /
           CAST(TotalInitialPosts AS DECIMAL(10, 0)), 'P3') AS PercentRetained
    FROM (
         SELECT
             SUM(IIF(new.Text NOT LIKE '%<' + @TagName + '>%', 1, 0)) AS NumberOfRemovals,
             COUNT(*) AS TotalInitialPosts
             FROM PostHistory orig
                 INNER JOIN
             PostHistory new ON orig.PostId = new.PostId AND new.CreationDate > orig.CreationDate
             WHERE (orig.PostHistoryTypeId = 3 OR orig.PostHistoryTypeId = 6)
               AND new.PostHistoryTypeId = 6
               AND new.CreationDate >= @DaysOffset AND orig.CreationDate >= @DaysOffset
               AND orig.Text LIKE '%<' + @TagName + '>%'
         ) as RemovalData

Enter Parameters

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