0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Ubuntu users and developers

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,
    COUNT(DISTINCT CASE WHEN u.Location LIKE '%' + t.TagName + '%' THEN u.Id END) as LocalUsers,
    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