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;


SELECT
t.TagName as Platform,
DATEPART(YEAR, p.CreationDate) as Year,
COUNT(DISTINCT p.Id) as Posts,
AVG(CAST(p.Score as FLOAT)) as AvgScore,
SUM(p.ViewCount) as TotalViews,
COUNT(DISTINCT CASE WHEN p.AcceptedAnswerId IS NOT NULL THEN p.Id END) as ResolvedIssues,
COUNT(DISTINCT ph.Id) as ContentChanges
FROM Posts p
LEFT JOIN PostTags pt ON p.Id = pt.PostId
LEFT JOIN Tags t ON pt.TagId = t.Id
LEFT JOIN PostHistory ph ON p.Id = ph.PostId
WHERE t.TagName IN ('expedia', 'booking-com', 'airbnb', 'hotels-com', 'tripadvisor')
AND p.PostTypeId = 1
GROUP BY t.TagName, DATEPART(YEAR, p.CreationDate)
ORDER BY Year DESC, Posts DESC;


SELECT 'https://travel.stackexchange.com/questions/' + CONVERT(VARCHAR, Posts.Id) as url,
(ViewCount / NULLIF((SELECT -DATEDIFF(DAY, GETDATE(), CreationDate)), 0)) AS avg_views_per_day,
Title,
Score,
ViewCount,
CreationDate
FROM Posts
LEFT JOIN PostTags ON Posts.Id = PostTags.PostId
LEFT JOIN Tags ON PostTags.TagId = Tags.Id
WHERE TagName IN ('booking', 'online-booking', 'expedia')
AND ViewCount > 1000
ORDER BY Score DESC;

fork query

edited dec 9

created dec 9 anonymous