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 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