0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for contractors and serious DIYers

SELECT
t.TagName as Region,
COUNT(DISTINCT p.Id) as Questions,
COUNT(DISTINCT a.Id) as Answers,
COUNT(DISTINCT u.Id) as UniqueUsers,
AVG(CAST(u.Reputation as FLOAT)) as AvgUserReputation,
SUM(p.ViewCount) as TotalViews
FROM Posts p
LEFT JOIN PostTags pt ON p.Id = pt.PostId
LEFT JOIN Tags t ON pt.TagId = t.Id
LEFT JOIN Posts a ON p.Id = a.ParentId
LEFT JOIN Users u ON p.OwnerUserId = u.Id
WHERE t.TagName IN ('india', 'china', 'japan', 'southeast-asia', 'europe', 'usa')
AND p.PostTypeId = 1
GROUP BY t.TagName
ORDER BY Questions DESC;

view current version

edited dec 12

created dec 12 anonymous