Unanswered, open first-questions by estimated views/day, weighted by your tag scores

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

DECLARE @userId INT = (
  SELECT Id FROM Users WHERE AccountId = ##NetworkAccountId:int##
);

DECLARE @now DATETIME = (
  SELECT MAX(CreationDate) FROM PostsWithDeleted
);

CREATE TABLE #FirstQuestionsRaw (
  Id INT NOT NULL PRIMARY KEY,
  CreationDate DATETIME NOT NULL,
  ViewCount INT NOT NULL
);

CREATE TABLE #FirstQuestions (
  Id INT NOT NULL PRIMARY KEY,
  CreationDate DATETIME NOT NULL,
  ViewCount INT NOT NULL,
  TagWeight INT NOT NULL
);

WITH FirstQuestionsRaw AS (
  SELECT
    RANK() OVER (PARTITION BY OwnerUserId ORDER BY Id) AS Rank,
    Id
  FROM Posts
  WHERE
    PostTypeId = 1
    AND
    OwnerUserId > 0
)
INSERT INTO #FirstQuestionsRaw
SELECT
  Posts.Id,
  Posts.CreationDate,
  Posts.ViewCount
FROM FirstQuestionsRaw, Posts
WHERE
  FirstQuestionsRaw.Id = Posts.Id
  AND
  FirstQuestionsRaw.Rank <= ##FirstNQuestions:int?3##
  AND
  Posts.ClosedDate IS NULL
  AND
  Posts.CommunityOwnedDate IS NULL
  AND
  Posts.AnswerCount = 0
  AND
  Posts.CreationDate < DATEADD(d, -7, @now);

WITH YourTagScores AS (
  SELECT PostTags.TagId, SUM(Score) AS Score
  FROM Posts, PostTags
  WHERE
    Posts.OwnerUserId = @userId
    AND
    PostTags.PostId = Posts.ParentId
  GROUP BY PostTags.TagId
  HAVING SUM(Score) > 0
)
INSERT INTO #FirstQuestions
SELECT
  #FirstQuestionsRaw.Id,
  MIN(#FirstQuestionsRaw.CreationDate) AS CreationDate,
  MIN(#FirstQuestionsRaw.ViewCount) AS ViewCount,
  SUM(YourTagScores.Score) AS TagWeight
FROM #FirstQuestionsRaw, PostTags, YourTagScores
WHERE
  #FirstQuestionsRaw.Id = PostTags.PostId
  AND
  PostTags.TagId = YourTagScores.TagId
GROUP BY #FirstQuestionsRaw.Id;

SELECT TOP 100
  Id AS [Post Link],
  CreationDate,
  ViewCount,
  --MIN(#FirstQuestions.Tags) AS Tags,
  86400.0 * ViewCount / DATEDIFF(s, CreationDate, @now)
    AS ViewsPerDay,
  86400.0 * ViewCount / DATEDIFF(s, CreationDate, @now) * TagWeight
    AS Goodness
FROM #FirstQuestions
ORDER BY Goodness DESC, Id

Enter Parameters

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