1

Please login or register to vote for this query.

(click on this box to dismiss)

rep greater than 1,000? yes -- "established user - You've been around for a while"

Q&A for professional and enthusiast programmers

CREATE TABLE #useraccepts (userid int, accepted int);

INSERT  #useraccepts (userid, accepted)
SELECT q.OwnerUserId, COUNT(q.Id)
FROM Posts q
WHERE 
q.PostTypeId = 1
AND q.AcceptedANswerId IS NOT NULL
AND q.ClosedDate IS NULL
GROUP BY q.OwnerUserId


CREATE TABLE #usernotaccepted (userid int, unaccepted int);
INSERT #usernotaccepted (userid, unaccepted)
SELECT q.OwnerUserId, COUNT(q.Id)
FROM Posts q
WHERE 
-- 1 = Question
q.PostTypeId = 1
-- Q has an accepted Answer
AND q.AcceptedANswerId IS NULL
-- Q is not closed
AND q.ClosedDate IS NULL
GROUP BY q.OwnerUserId

SELECT 
  #useraccepts.userid as [User Link], 
  accepted, 
  unaccepted, 
  (100 * accepted / (accepted + unaccepted)) as ratio,
  u.reputation,
  (1+datediff(dd, u.creationdate, GETDATE())) "Age in Days"
FROM #useraccepts
JOIN #usernotaccepted
ON #useraccepts.userid = #usernotaccepted.userid
JOIN Users u
ON #useraccepts.userid = u.id
WHERE u.reputation > 1000
ORDER BY unaccepted DESC

fork query

edited aug 5 17

created aug 2 17 Jeff Schaller