0

Please login or register to vote for this query.

(click on this box to dismiss)

compare outcomes for questions tagged 'ant, java' with those just tagged 'ant'

Q&A for Ubuntu users and developers

-- Tag smackdown: java and ant versus ant
-- compare outcomes for questions tagged 'ant, java' with those just tagged 'ant'

SELECT 
    CASE 
    WHEN ptj.PostId IS NULL
    THEN
         'ant'
    ELSE 'ant and java'
    END AS 'Tagging',
    COUNT( p.Id ) AS Posts,
    AVG( CAST( p.Score AS FLOAT ) ) AS 'Avg. Question Score',
    AVG( CAST( p.ViewCount AS FLOAT ) / DATEDIFF( DAY, p.CreationDate, GETUTCDATE() ) ) AS 'Avg. Views Per Day',
    AVG( CAST( ISNULL( p.AnswerCount, 0 ) AS FLOAT ) ) AS 'Avg. Answer Count',
    AVG( CAST( CASE WHEN p.AcceptedAnswerId IS NULL THEN 0 ELSE 1 END AS FLOAT ) ) AS 'Accepted Rate'
FROM
    Posts p

LEFT JOIN
   PostTags ptj
ON    ptj.PostId = p.Id
AND   ptj.TagId = ( SELECT id FROM Tags where TagName = 'java' )

WHERE p.Score < 20 AND p.Id IN (
SELECT
        pta.PostId
FROM
      PostTags pta

INNER JOIN
      Tags ta
ON    ta.Id = pta.TagId
AND   ta.TagName = 'ant'
)
GROUP BY
    CASE 
    WHEN ptj.PostId IS NULL
    THEN
         'ant'
    ELSE 'ant and java'
    END

fork query

created feb 8 11 martin clayton