Answers as a function of feedback

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT p.Id,
       p.OwnerUserId,
       p.CreationDate,
       p.Score,
       q.AcceptedAnswerId, 
       ROW_NUMBER() OVER (PARTITION BY p.OwnerUserId ORDER BY p.OwnerUserId, p.CreationDate ASC) AS RowNum
INTO #posts
FROM Posts p
INNER JOIN Users u
ON (
  p.OwnerUserId = u.Id AND
  p.PostTypeId = 2 AND
  u.CreationDate > '2017-01-01' AND
  u.CreationDate < '2019-01-01'
)
INNER JOIN Posts q
ON (
  p.ParentId = q.Id
) 
ORDER BY p.OwnerUserId, p.CreationDate DESC;

SELECT *
INTO #tenthpost
FROM #posts 
WHERE RowNum = 10;

SELECT
  tp.OwnerUserId,
  MAX(tp.Score) AS Score10,
  MAX(tp.CreationDate) AS CreationDate10,
  SUM(CASE WHEN p.AcceptedAnswerId = p.Id THEN 1 ELSE 0 END) AS CountAccept
INTO #posts0to9
FROM #tenthpost tp
INNER JOIN #posts p
ON (
  tp.OwnerUserId = p.OwnerUserId AND
  p.RowNum < 10
) GROUP BY tp.OwnerUserId;

SELECT
  tp.OwnerUserId,
  COUNT(p.OwnerUserId) AS CountAfter10
INTO #after10
FROM #tenthpost tp
INNER JOIN #posts p
ON (
  tp.OwnerUserId = p.OwnerUserId AND 
  (p.CreationDate BETWEEN tp.CreationDate AND DATEADD(DAY,100,tp.CreationDate))
) GROUP BY tp.OwnerUserId;

SELECT * FROM #posts0to9 f
INNER JOIN #after10 s
ON (f.OwnerUserId = s.OwnerUserId);

Enter Parameters

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