0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

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;

fork query

edited dec 12

created dec 12 anonymous