Unanswered Questions per New User by Quarter

0

Please login or register to vote for this query.

(click on this box to dismiss)

Monero Meta

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

with QuestionQuarters as (
    SELECT
        DATEPART(yyyy, Question.CreationDate) as Year,
        DATEPART(q, Question.CreationDate) as Quarter,
        COUNT(*) as Total,
        SUM(CASE when Question.AnswerCount > 0 then 1 else 0 END) as Answered
    FROM Posts as Question
    LEFT JOIN Posts as Answer on Answer.PostTypeId=2 and Answer.ParentId=Question.Id and Answer.Score > 0
    WHERE Question.PostTypeId=1 AND Question.ClosedDate is null
    GROUP BY DATEPART(yyyy, Question.CreationDate),DATEPART(q, Question.CreationDate)
),

UserQuarters as (
    SELECT
        DATEPART(yyyy, CreationDate) as Year,
        DATEPART(q, CreationDate) as Quarter,
        COUNT(*) as UserCount
    FROM Users
    GROUP BY DATEPART(yyyy, CreationDate),DATEPART(q, CreationDate)
)

select
    QuestionQuarters.Year,
    QuestionQuarters.Quarter,
    convert(numeric(5,2), QuestionQuarters.Total / CAST(UserCount as float)) as QuestionsPerUser
FROM QuestionQuarters
LEFT JOIN UserQuarters on QuestionQuarters.Year=UserQuarters.Year and QuestionQuarters.Quarter=UserQuarters.Quarter
order by Year, Quarter, UserCount

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results