0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for contractors and serious DIYers

SELECT
    Users.location as Location,
    Tags.TagName as TransportType,
    COUNT(DISTINCT Posts.Id) as QuestionCount,
    AVG(CAST(Posts.Score as FLOAT)) as AvgScore,
    SUM(Posts.ViewCount) as TotalViews
FROM Posts
LEFT JOIN PostTags ON Posts.Id = PostTags.PostId
LEFT JOIN Tags ON PostTags.TagId = Tags.Id
LEFT JOIN Users ON Posts.OwnerUserId = Users.Id
WHERE Tags.TagName IN ('air-travel', 'trains', 'car-rentals', 'public-transport', 'cruise')
  AND Posts.PostTypeId = 1
  AND YEAR(Posts.CreationDate) IN (2020, 2021,2022, 2023, 2024) -- 2023年と2024年の投稿に限定
GROUP BY Users.Location, Tags.TagName
ORDER BY Users.Location, Tags.TagName, QuestionCount DESC;

fork query

edited dec 12

created dec 12 anonymous