Is the rate of answered questions declining?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Finds the total number of questions and also the number of answered questions (questions with an accepted answer or at least one answer with at least one upvote) for the last 22 months

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- Is the rate of answered questions declining?
-- Finds the total number of questions and also the number of 
-- answered questions (questions with an accepted answer or at least one
-- answer with at least one upvote) for the last 22 months

create table #tmp_dates (date datetime)
insert into #tmp_dates values ('20090801')
insert into #tmp_dates values ('20090901')
insert into #tmp_dates values ('20091001')
insert into #tmp_dates values ('20091101')
insert into #tmp_dates values ('20091201')
insert into #tmp_dates values ('20100101')
insert into #tmp_dates values ('20100201')
insert into #tmp_dates values ('20100301')
insert into #tmp_dates values ('20100401')
insert into #tmp_dates values ('20100501')
insert into #tmp_dates values ('20100601')
insert into #tmp_dates values ('20100701')
insert into #tmp_dates values ('20100801')
insert into #tmp_dates values ('20100901')
insert into #tmp_dates values ('20101001')
insert into #tmp_dates values ('20101101')
insert into #tmp_dates values ('20101201')
insert into #tmp_dates values ('20110101')
insert into #tmp_dates values ('20110201')
insert into #tmp_dates values ('20110301')
insert into #tmp_dates values ('20110401')
insert into #tmp_dates values ('20110501')

SELECT date, 
       (SELECT Count(*) FROM Posts
           WHERE PostTypeId = 1 
           AND ClosedDate IS NULL
           AND CreationDate < date
       ) AS total_questions,
       (SELECT COUNT(*) FROM Posts AS q
            WHERE PostTypeId=1
            AND ClosedDate IS NULL
            AND CreationDate < date
            AND (AcceptedAnswerId IS NOT NULL
                 OR
                 (SELECT COUNT(*) FROM Posts AS a WHERE PostTypeId=2 AND ParentId=q.id AND Score > 0) > 0)
       ) AS answered_questions
FROM #tmp_dates

Enter Parameters

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