0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for music historians, critics, and fans

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;

fork query

edited dec 7

created dec 6 anonymous