0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for developers and users of the secure, private and untraceable cryptocurrency Monero

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