Tags most likely deleted

1

Please login or register to vote for this query.

(click on this box to dismiss)

Tezos

Q&A for users, experts, and developers of the Tezos blockchain project

WITH Raw AS (
  SELECT Id, st.Value as TagName
  FROM PostsWithDeleted
  CROSS APPLY STRING_SPLIT (
    LTRIM(REPLACE(REPLACE(Tags, '<', ' '), '>', '')), ' ') st
  WHERE
    DeletionDate IS NOT NULL
),
PostTagsWithDeleted AS (
  SELECT Raw.Id AS PostId, Tags.Id AS TagId
  FROM Raw, Tags
  WHERE
    Raw.TagName = Tags.TagName

  UNION

  SELECT PostId, TagId
  FROM PostTags
),
Raw2 AS (
  SELECT
    PostTagsWithDeleted.TagId,
    COUNT(*) AS AllCount,
    COUNT(PostsWithDeleted.DeletionDate) AS DelCount
  FROM PostTagsWithDeleted, PostsWithDeleted
  WHERE
    PostTagsWithDeleted.PostId = PostsWithDeleted.Id
  GROUP BY
    PostTagsWithDeleted.TagId
  HAVING COUNT(*) > ##MinCount:int?1000##
)
SELECT TOP ##TopN:int?100##
  Raw2.TagId,
  Tags.TagName,
  Raw2.AllCount,
  Raw2.DelCount,
  100.0 * Raw2.DelCount / Raw2.AllCount AS PctDeleted
FROM Raw2, Tags
WHERE Tags.Id = Raw2.TagId
ORDER BY PctDeleted DESC, Raw2.TagId

Enter Parameters

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