Your tag topnesses

1

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS

Q&A for administrators, end users, developers and designers for Craft CMS

WITH Raw AS (
  SELECT
    PostTags.TagId,
    A.OwnerUserId AS UserId,
    SUM(A.Score) AS Score
  FROM Posts A, PostTags
  WHERE
    A.ParentId = PostTags.PostId
    AND
    A.OwnerUserId > 0
    AND
    A.CommunityOwnedDate IS NULL
  GROUP BY A.OwnerUserId, PostTags.TagId
),
Raw2 AS (
  SELECT
    Raw.TagId,
    Raw.UserId,
    Raw.Score,
    DENSE_RANK() OVER (PARTITION BY Raw.TagId ORDER BY Raw.Score DESC)
      AS Rank
  FROM Raw
  WHERE
    Raw.Score > 0
)
SELECT Tags.TagName, Raw2.Score, Raw2.Rank
FROM Raw2, Users, Tags
WHERE
  Raw2.UserId = Users.Id
  AND
  Raw2.TagId = Tags.Id
  AND
  Users.AccountId = ##SEAccountId:int##
ORDER BY Rank, TagId

Enter Parameters

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