1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

WITH Raw AS (
  SELECT
    PostsWithDeleted.Id,
    PostsWithDeleted.ClosedDate,
    PostsWithDeleted.DeletionDate,
    T.Value AS TagName
  FROM PostsWithDeleted
  CROSS APPLY
    STRING_SPLIT (LTRIM(REPLACE(REPLACE(Tags, '<', ' '), '>', '')), ' ') T
  WHERE
    PostsWithDeleted.DeletionDate IS NOT NULL
    AND
    PostsWithDeleted.PostTypeId = 1
),
Raw2 AS (
  SELECT
    Raw.Id,
    Raw.ClosedDate,
    Raw.DeletionDate,
    Tags.Id AS TagId
  FROM Raw, Tags
  WHERE
    Raw.TagName = Tags.TagName
  
  UNION
  
  SELECT
    Posts.Id,
    Posts.ClosedDate,
    NULL AS DeletionDate,
    PostTags.TagId
  FROM Posts, PostTags
  WHERE
    Posts.Id = PostTags.PostId
),
Raw3 AS (
  SELECT
    TagId,
    COUNT(
      CASE
        WHEN ClosedDate IS NOT NULL OR DeletionDate IS NOT NULL
        THEN 1
        END)
    AS KilledCount,
    COUNT(*) AS AllCount
  FROM Raw2
  GROUP BY TagId
)
SELECT TOP 100
  Tags.TagName,
  Raw3.AllCount - Raw3.KilledCount AS SurvivalCount,
  Raw3.*,
  100.0 * KilledCount / AllCount AS KilledPct
FROM Raw3, Tags
WHERE
  Raw3.AllCount >= ##MinTagCount:int?1000##
  AND
  Tags.Id = Raw3.TagId
ORDER BY KilledPct;

fork query

edited feb 24

created feb 24 peterh - PLEASE DELETE THIS ACCOUNT