1

Please login or register to vote for this query.

(click on this box to dismiss)

Percentage of open questions that are Good (Score > 0) and Bad (Score < 0), by quarter

Q&A for Ubuntu users and developers

with quarters as (
    SELECT
        DATEPART(yyyy, CreationDate) as Year,
        DATEPART(q, CreationDate) as Quarter,
        COUNT(*) as Total,
        SUM(CASE when Score > 0 then 1 else 0 END) as PositiveScore,
        SUM(CASE when Score < 0 then 1 else 0 END) as NegativeScore
    FROM Posts
    WHERE PostTypeId=1 AND ClosedDate is null
    GROUP BY DATEPART(yyyy, CreationDate),DATEPART(q, CreationDate)
)

select
    Year,
    Quarter,
    Total as Questions,
    CAST(convert(numeric(5,2), cast(PositiveScore as float) * 100 / Total) as VARCHAR(10)) + '%' as Good,
    CAST(convert(numeric(5,2), cast(NegativeScore as float) * 100 / Total) as VARCHAR(10)) + '%' as Bad
from quarters
order by Year, Quarter

fork query

edited aug 11 12

created aug 11 12 Brad Mace