Q&A for developers of open-source hardware and software that is compatible with Arduino
SELECT Users.Id as uid, Count(DISTINCT Posts.Id) as cnt, Sum(Posts.Score) as scr INTO #user_count FROM Users INNER JOIN Posts ON Posts.OwnerUserId = Users.Id WHERE Posts.PostTypeId = 2 GROUP BY Users.Id; SELECT TOP 500 Users.Id AS [User Link], Count(DISTINCT ClosedQuestions.Id) as ClosedAnswerCount, Max(ucnt.cnt) as TotalAnswerCount, ROUND(100*CAST(Count(DISTINCT ClosedQuestions.Id) as float)/Max(ucnt.cnt) ,2) AS PercentAnswersClosed, Sum(ClosedAnswers.Score) as ClosedScore, Max(ucnt.scr) as TotalScore, ROUND(100*Cast(Sum(ClosedAnswers.Score) as float)/Max(ucnt.scr),2) as PercentScoreClosed FROM Users INNER JOIN Posts as ClosedAnswers ON ClosedAnswers.OwnerUserId = Users.Id INNER JOIN Posts as ClosedQuestions ON ClosedQuestions.id = ClosedAnswers.ParentId INNER JOIN #user_count as ucnt ON ucnt.uid = Users.Id WHERE ClosedAnswers.PostTypeId = 2 AND ClosedAnswers.CommunityOwnedDate is null AND ClosedQuestions.PostTypeId = 1 AND ClosedQuestions.ClosedDate is not null AND ucnt.cnt > 500 GROUP BY Users.Id ORDER BY PercentAnswersClosed DESC;