0

Please login or register to vote for this query.

(click on this box to dismiss)

compare performance of question tagged 'ant java' with those just tagged 'ant'

Q&A for Ubuntu users and developers

-- Tag smackdown: java-ant versus ant
-- compare performance of question tagged 'ant java' with those just tagged 'ant'

SELECT 
    CASE 
    WHEN ptj.PostId IS NULL
    THEN
         'ant'
    ELSE 'ant and java'
    END AS Tags,
    COUNT( p.Id ) AS Posts,
    AVG( CAST( p.Score AS FLOAT ) ) AS Avg_Q_Score,
    AVG( CAST( p.ViewCount AS FLOAT ) / DATEDIFF( DAY, p.CreationDate, GETUTCDATE() ) ) AS Avg_ViewsPerDay,
    AVG( CAST( ISNULL( p.AnswerCount, 0 ) AS FLOAT ) ) AS Avg_AnswerCount,
    AVG( CAST( CASE WHEN p.AcceptedAnswerId IS NULL THEN 0 ELSE 1 END AS FLOAT ) ) AS AcceptedRate
FROM
    Posts p

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

WHERE 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