0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

SELECT 
    p.Title,
    p.Score,
    p.ViewCount,
    CASE 
        WHEN p.AcceptedAnswerId IS NOT NULL THEN 'Resolved'
        WHEN p.AnswerCount > 0 THEN 'Has Answers'
        ELSE 'Unresolved'
    END as ResolutionStatus,
    DATEDIFF(HOUR, p.CreationDate, 
        COALESCE(
            (SELECT MIN(CreationDate) 
             FROM Posts a 
             WHERE a.ParentId = p.Id),
            p.LastActivityDate
        )
    ) as HoursToFirstResponse,
    COUNT(DISTINCT c.Id) as CommentCount,
    'https://travel.stackexchange.com/questions/' + CAST(p.Id as VARCHAR) as QuestionURL
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
    AND t.TagName IN ('customer-service', 'support', 'booking-problems')
GROUP BY p.Id, p.Title, p.Score, p.ViewCount, p.AcceptedAnswerId, 
         p.AnswerCount, p.CreationDate, p.LastActivityDate
HAVING p.Score > 2
ORDER BY p.Score DESC;

fork query

created dec 7 anonymous