0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

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 7

created dec 6 anonymous