1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

DECLARE @UserID Int = ##UserID?9449426##
DECLARE @TagName Nvarchar(Max) = LOWER('##TagName?reactjs##')
DECLARE @MinDate DateTime = DATEADD(MONTH, -3, GETDATE())
DECLARE @Now DateTime = GETDATE()

-- Answers to recent questions that have lower score than the questions
SELECT
  q.Id [Post Link],
  q.CreationDate,
  q.Score [Question Score],
  a.Score [Answer Score],
  q.Score - a.Score [Diff]
FROM Posts a
LEFT JOIN Posts q ON q.Id = a.ParentId
WHERE a.OwnerUserId = @UserID
  AND a.CreationDate > @MinDate
  -- not new answer in old question
  AND DATEDIFF(day, q.CreationDate, a.CreationDate) < 7
  AND q.Score > a.Score
ORDER BY Diff DESC

-- Answers to recent questions that have the most view
SELECT
  q.Id [Post Link],
  q.CreationDate,
  q.Score [Question Score],
  a.Score [Answer Score],
  q.ViewCount
FROM Posts a
LEFT JOIN Posts q ON q.Id = a.ParentId
WHERE a.OwnerUserId = @UserID
  AND a.CreationDate > @MinDate
  AND DATEDIFF(day, q.CreationDate, a.CreationDate) < 7
ORDER BY ViewCount DESC

-- Recent questions that have the most view
SELECT TOP 200
  q.Id [Post Link],
  q.CreationDate,
  q.Score [Question Score],
  q.ViewCount
FROM Posts q
WHERE DATEDIFF(day, q.CreationDate, @Now) < 100
  AND LOWER(q.Tags) LIKE '%' + @TagName + '%'
ORDER BY ViewCount DESC

fork query

edited oct 31 21

created oct 13 21 NearHuscarl