How to improve your Developer Story effectively? (better)

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)
);

DECLARE @Pcts TABLE (
  Pct FLOAT NOT NULL PRIMARY KEY
);

CREATE TABLE #Tags (
  TagId INT NOT NULL PRIMARY KEY,
  YourScore INT NOT NULL
);

INSERT INTO @Pcts
SELECT 0.01 AS Pct
UNION
SELECT 0.05
UNION
SELECT 0.1
UNION
SELECT 0.2;

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;

INSERT INTO #Tags
SELECT T.TagId, COALESCE(#UserTagScores.Score, 0) AS YourScore
FROM (
  SELECT TagId
  FROM #TagScorePct
  GROUP BY TagId
) T
LEFT JOIN #UserTagScores
  ON T.TagId = #UserTagScores.TagId AND #UserTagScores.UserId = @UserId;

SELECT * FROM #Tags ORDER BY YourScore DESC;

/*
WITH 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