1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

WITH Raw AS (
  SELECT Tags.Id AS TagId, W.OwnerUserId AS UserId
  FROM Tags, Posts W, Posts E
  WHERE
    Tags.ExcerptPostId = E.Id
    AND
    Tags.WikiPostId = W.Id
    AND
    E.OwnerUserId = W.OwnerUserId
    AND
    E.OwnerUserId > 0
),
Raw2 AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    PostTags.TagId,
    SUM(Posts.Score) AS TagScore
  FROM Posts, PostTags
  WHERE
    PostTags.TagId IN (SELECT TagId FROM Raw)
    AND
    Posts.ParentId = PostTags.PostId
    AND
    Posts.OwnerUserId > 0
  GROUP BY Posts.OwnerUserId, PostTags.TagId
),
Raw3 AS (
  SELECT
    TagId,
    MAX(TagScore) AS MaxTagScore
  FROM Raw2
  GROUP BY TagId
),
Raw4 AS (
  SELECT
    Raw2.TagId,
    Raw2.UserId
  FROM Raw2, Raw3
  WHERE
    Raw2.TagId = Raw3.TagId
    AND
    Raw3.MaxTagScore = Raw2.TagScore
),
Raw5 AS (
  SELECT *
  FROM Raw
  INTERSECT
  SELECT *
  FROM Raw4
)
SELECT
  Raw5.UserId AS [User Link],
  Tags.TagName,
  Tags.Count,
  Raw3.MaxTagScore
FROM Raw5, Raw3, Tags
WHERE
  Raw5.TagId = Raw3.TagId
  AND
  Raw5.TagId = Tags.Id
ORDER BY Tags.Count DESC, Raw5.TagId

fork query

edited may 15 20

created may 15 20 peterh - PLEASE DELETE THIS ACCOUNT