Most viewed posts with tags became synonyms

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 @DumpTime DATETIME;
SELECT @DumpTime = MAX(CreationDate) FROM Posts;

WITH Raw AS (
  SELECT TagName
  FROM Tags
  
  INTERSECT
  
  SELECT SourceTagName AS TagName
  FROM TagSynonyms
  WHERE ApprovalDate IS NOT NULL
)
SELECT TOP 20000
  DENSE_RANK() OVER (ORDER BY Posts.ViewCount DESC) AS N,
  Posts.Id AS [Post Link],
  Posts.CreationDate,
  Posts.ViewCount,
  1.0 * Posts.ViewCount / DATEDIFF(s, Posts.CreationDate, @DumpTime)
    AS ViewsPerSec,
  'site://tags/' + TagSynonyms.SourceTagName + '|' + TagSynonyms.SourceTagName
    AS SourceTag,
  'site://tags/' + TagSynonyms.TargetTagName + '|' + TagSynonyms.TargetTagName
    AS TargetTag
FROM Raw, Tags, PostTags, TagSynonyms, Posts
WHERE
  Raw.TagName = Tags.TagName
  AND
  Tags.Id = PostTags.TagId
  AND
  PostTags.PostId = Posts.Id
  AND
  Raw.TagName = TagSynonyms.SourceTagName
ORDER BY
  ViewsPerSec DESC, Posts.Id

Enter Parameters

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