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 Topic,
    COUNT(DISTINCT p.Id) as Questions,
    COUNT(DISTINCT a.Id) as Answers,
    AVG(CAST(p.Score AS FLOAT)) as AvgScore,
    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 -- 回答テーブルとしてPostsを再度JOIN
LEFT JOIN Users u ON p.OwnerUserId = u.Id
WHERE u.Location LIKE '%USA%'
  AND t.TagName IN ('europe', 'trains', 'public-transport', 'railways')
  AND p.PostTypeId = 1 -- 質問のみ
GROUP BY t.TagName
ORDER BY TotalViews DESC;

view current version

edited dec 12

created dec 12 anonymous