0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

DECLARE @location VARCHAR(255) = '##location##'
DECLARE @tagname VARCHAR(255) = '##tagname##'
DECLARE @ndays INT = '##ndays##'
DECLARE @locationLower VARCHAR(255) = LOWER('%' + @location + '%')

;WITH UserLocations AS 
(
  SELECT
    Id,
    LOWER(Location) AS Location,
    WebsiteUrl,
    AboutMe
  FROM Users
  WHERE LOWER(Location) LIKE @locationLower
),

TaggedPosts AS 
(
  SELECT
    Posts.Id,
    OwnerUserId,
    Score,
    LastActivityDate,
    PostTypeId
  FROM Posts
  INNER JOIN PostTags ON Posts.Id = PostTags.PostId
  INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.Tagname = @tagname
),

QuestionScores AS
(
  SELECT
    UserLocations.Id AS [User Link],
    UserLocations.WebsiteUrl,
    UserLocations.AboutMe,
    SUM(TaggedPosts.Score) AS Score,
    MAX(TaggedPosts.LastActivityDate) As Date
  FROM TaggedPosts 
  INNER JOIN UserLocations ON TaggedPosts.OwnerUserId = UserLocations.Id
  WHERE TaggedPosts.PostTypeId = 1
  GROUP BY UserLocations.Id, UserLocations.WebsiteUrl, UserLocations.AboutMe
),

AnswerScores AS
(
  SELECT
    UserLocations.Id AS [User Link],
    UserLocations.WebsiteUrl,
    UserLocations.AboutMe,
    SUM(TaggedPosts.Score) AS Score,
    MAX(TaggedPosts.LastActivityDate) As Date
  FROM TaggedPosts 
  INNER JOIN UserLocations ON TaggedPosts.OwnerUserId = UserLocations.Id
  WHERE TaggedPosts.PostTypeId = 2
  GROUP BY UserLocations.Id, UserLocations.WebsiteUrl, UserLocations.AboutMe
)

SELECT 
  TOP 150
  ISNULL(QuestionScores.[User Link], AnswerScores.[User Link]) as [User Link],
  ISNULL(QuestionScores.WebsiteUrl, AnswerScores.WebsiteUrl) as [Website Url],
  ISNULL(QuestionScores.AboutMe, AnswerScores.AboutMe) as [About Me],
  ISNULL(QuestionScores.Score, 0) as [Question Score],
  ISNULL(AnswerScores.Score, 0) as [Answer Score],
  ISNULL(QuestionScores.Score, 0) + ISNULL(AnswerScores.Score, 0) as [Total Score]
FROM
  QuestionScores
FULL JOIN AnswerScores ON QuestionScores.[User Link] = AnswerScores.[User Link]
WHERE
  DateDiff(day, ISNULL(QuestionScores.Date, AnswerScores.Date), getDate()) < @ndays
ORDER BY
  [Total Score] DESC;

fork query

edited sep 27

created sep 11 anonymous