How to improve your Developer Story effectively?

1

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla Meta

Q&A about the site for Joomla! administrators, users, developers and designers

DECLARE @UserId INT = (
  CASE
    WHEN ##SiteAccountId:int?0## > 0 THEN ##SiteAccountId##
    ELSE (
      SELECT Id FROM Users WHERE AccountId = ##NetworkAccountId:int?0##
    )
  END
);

CREATE TABLE #UserTagScores (
  UserId INT NOT NULL,
  TagId INT NOT NULL,
  Score INT NOT NULL,
  PRIMARY KEY(UserId, TagId)
);

CREATE TABLE #TagScorePct (
  TagId INT NOT NULL,
  Score INT NOT NULL,
  Pct FLOAT NOT NULL,
  PRIMARY KEY(TagId, Score)
);

INSERT INTO #UserTagScores
SELECT
  Posts.OwnerUserId AS UserId,
  PostTags.TagId,
  SUM(Posts.Score) AS Score
FROM Posts, PostTags, Tags
WHERE
  Posts.ParentId = PostTags.PostId
  AND
  PostTags.TagId = Tags.Id
  AND
  Tags.Count >= 1000
GROUP BY Posts.OwnerUserId, PostTags.TagId
HAVING Posts.OwnerUserId > 0 AND COUNT(*) > 1;

WITH TagScoreUserCountRaw AS (
  SELECT TagId, Score, COUNT(*) AS UserCount
  FROM #UserTagScores
  GROUP BY TagId, Score
),
TagScoreUserCount AS (
  SELECT
    TagId,
    Score,
    UserCount,
    SUM(UserCount) OVER (PARTITION BY TagId ORDER BY Score DESC)
      AS CumCount
  FROM TagScoreUserCountRaw
),
TagUserCount AS (
  SELECT TagId, COUNT(*) AS UserCount
  FROM #UserTagScores
  GROUP BY TagId
)
INSERT INTO #TagScorePct
SELECT
  TagScoreUserCount.TagId,
  TagScoreUserCount.Score,
  100.0 * TagScoreUserCount.CumCount / TagUserCount.UserCount AS Pct
FROM TagScoreUserCount, TagUserCount
WHERE
  TagScoreUserCount.TagId = TagUserCount.TagId;

WITH Pcts AS (
  SELECT 1 AS Pct
  UNION
  SELECT 5
  UNION
  SELECT 10
  UNION
  SELECT 20
),
Raw AS (
  SELECT
    #TagScorePct.TagId,
    CAST(Pcts.Pct AS VARCHAR(MAX)) + '%' AS Pct,
    MIN(Score) AS MinScore
  FROM #TagScorePct, Pcts
  WHERE #TagScorePct.Pct < Pcts.Pct
  GROUP BY #TagScorePct.TagId, Pcts.Pct
),
Raw2 AS (
  SELECT *
  FROM Raw
  PIVOT (
    MIN(MinScore) FOR Pct IN ([1%], [5%], [10%], [20%])
  ) AS Pivot_table
)
SELECT
  Tags.TagName,
  Raw2.*,
  #UserTagScores.Score AS YourTagScore,
  #TagScorePct.Pct AS YourPercentile
FROM Raw2, #UserTagScores, Tags, #TagScorePct
WHERE
  Raw2.TagId = #UserTagScores.TagId
  AND
  #UserTagScores.UserId = @UserId
  AND
  Tags.Id = Raw2.TagId
  AND
  Tags.Id = #TagScorePct.TagId
  AND
  #UserTagScores.Score = #TagScorePct.Score
ORDER BY YourPercentile, YourTagScore DESC, Tags.Id

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results