0

Please login or register to vote for this query.

(click on this box to dismiss)

This might give some interesting insight in the types of tags used together..

Q&A for community managers, administrators, and moderators

DECLARE @Custom TABLE
(
      Original nvarchar(255),
      Tag nvarchar(255),
      Count int
)

DECLARE @total float
SELECT @total = Sum(Cast(ViewCount as bigint)) FROM Posts WHERE PostTypeId = 1

DECLARE @TagName nvarchar(255)

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
  SELECT
    Tags.Tagname
  FROM Posts 
    JOIN PostTags ON Posts.Id = PostTags.PostId
    JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Posts.PostTypeId = 1 
  GROUP BY Tags.Tagname
  ORDER BY Sum(Cast(Posts.ViewCount as bigint)) DESC
  OFFSET 5 ROWS
  FETCH NEXT 10 ROWS ONLY;

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @TagName
WHILE @@FETCH_STATUS = 0
BEGIN 
    PRINT @TagName
    INSERT INTO @Custom (Original, Tag, Count)
    SELECT Top 5
      @TagName as Original,
      Tags.TagName as Tag,
      COUNT(*) AS Count
    
    FROM PostTags
      INNER JOIN Tags
        ON Tags.Id = PostTags.TagId
    WHERE 
      PostTags.PostId IN 
      (
        --Gets all the PostIds that have this tag
        SELECT DISTINCT PostId
        FROM PostTags
          INNER JOIN Tags
            ON Tags.Id = PostTags.TagId
        WHERE Tags.TagName = @TagName
      )
      AND Tags.TagName <> @TagName
    GROUP BY Tags.TagName, Tags.Count
    ORDER BY COUNT(*) DESC, Tags.Count, Tags.TagName

    FETCH NEXT FROM MY_CURSOR INTO @TagName
END

SELECT * from @Custom
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

fork query

edited mar 27 18 anonymous

created jul 1 11 TomWij