0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for community managers, administrators, and moderators

SELECT
Users.Location, COUNT(*) AS QuestionCount
FROM Users
JOIN Posts ON Users.Id = Posts.OwnerUserId
WHERE Users.Location IS NOT NULL
GROUP BY Users.Location
ORDER BY QuestionCount DESC;


SELECT
Posts.Title,
Posts.Score,
Posts.ViewCount,
Posts.AnswerCount,
'https://travel.stackexchange.com/questions/' + CAST(Posts.Id as VARCHAR) as QuestionURL
FROM Posts
LEFT JOIN PostTags ON Posts.Id = PostTags.PostId
LEFT JOIN Tags ON PostTags.TagId = Tags.Id
WHERE Tags.TagName IN ('vacation-rentals', 'apartments', 'airbnb')
AND Posts.PostTypeId = 1
AND Posts.Score > 5
ORDER BY Posts.Score DESC;


SELECT
Users.Location,
Tags.TagName,
COUNT(*) as PostCount,
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 Users.Id = Posts.OwnerUserId
WHERE Posts.CreationDate >= DATEADD(YEAR, -2, GETDATE())
AND Posts.PostTypeId = 1
GROUP BY Users.Location, Tags.TagName
HAVING COUNT(*) > 1
ORDER BY PostCount DESC;


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
GROUP BY Users.Location, Tags.TagName
ORDER BY Users.Location, Tags.TagName, QuestionCount DESC;


SELECT TOP 100
p.Id as PostId,
p.Title,
p.Score as PostScore,
p.ViewCount,
COUNT(DISTINCT c.Id) as CommentCount,
AVG(CAST(c.Score as FLOAT)) as AvgCommentScore,
'https://travel.stackexchange.com/questions/' + CAST(p.Id as VARCHAR) as QuestionURL,
p.CreationDate,
p.LastActivityDate
FROM Posts p
LEFT JOIN Comments c ON p.Id = c.PostId
LEFT JOIN PostTags pt ON p.Id = pt.PostId
LEFT JOIN Tags t ON pt.TagId = t.Id
WHERE p.PostTypeId = 1 -- Questions only
AND (
t.TagName IN ('booking', 'customer-service', 'online-booking')
OR p.Title LIKE '%trust%'
OR p.Title LIKE '%scam%'
OR p.Title LIKE '%reliable%'
)
GROUP BY p.Id, p.Title, p.Score, p.ViewCount, p.CreationDate, p.LastActivityDate
HAVING COUNT(DISTINCT c.Id) > 5
ORDER BY p.Score DESC;


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 9

created dec 9 anonymous