0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Top Answeres and Accepted Rate by Country
-- Created by mahdijoon (https://stackoverflow.com/users/11434373/mahdijoon)

SELECT TOP 100
  u.Id AS [User],
  u.DisplayName, u.Reputation, u.Location, u.LastAccessDate,
  COUNT(*) AS AnswerCount,
  SUM(case when q.AcceptedAnswerId = a.Id then 1 else 0 end) AS AcceptAnswerCount,
  (SUM(case when q.AcceptedAnswerId = a.Id then 1 else 0 end) * 100.0 / COUNT(*)) AS AcceptRate,
  MAX(a.CreationDate) AS LastAnswerCreationDate
FROM Posts a
INNER JOIN Users u ON u.Id = a.OwnerUserId AND LOWER(u.Location) LIKE LOWER('%##CountryName##%')
INNER JOIN Posts q ON a.ParentId = q.Id
WHERE 
(q.OwnerUserId <> u.Id OR q.OwnerUserId IS NULL)   --no self answers
GROUP BY u.Id, u.DisplayName, u.Reputation, u.Location, u.LastAccessDate
ORDER BY AnswerCount DESC

fork query

edited jan 15 Roman R.

created jan 9 MahdiJoon