0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Ubuntu users and developers

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
INNER JOIN PostTags pt ON p.Id = pt.PostId
INNER JOIN Tags t ON pt.TagId = t.Id
LEFT JOIN Comments c ON p.Id = c.PostId 
WHERE p.PostTypeId = 1  -- Questions only
    AND p.Score > 0     -- Add score filter to reduce data
    AND p.CreationDate >= DATEADD(YEAR, -5, GETDATE())  -- Last 5 years only
    AND (
        t.TagName IN ('booking', 'customer-service', 'online-booking')
        OR p.Title LIKE '%trust%'  -- Simplified title search
    )
GROUP BY 
    p.Id, 
    p.Title, 
    p.Score, 
    p.ViewCount, 
    p.CreationDate, 
    p.LastActivityDate
HAVING COUNT(DISTINCT c.Id) >= 2  -- Reduced comment threshold
ORDER BY p.Score DESC;

fork query

edited dec 7

created dec 7 anonymous