1

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 and also calculates the ratio

Q&A for cartographers, geographers and GIS professionals

-- 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 36 months
-- and also calculates the ratio

create table #tmp_dates (date datetime)
insert into #tmp_dates values ('20110501')
insert into #tmp_dates values ('20110601')
insert into #tmp_dates values ('20110701')
insert into #tmp_dates values ('20110801')
insert into #tmp_dates values ('20110901')
insert into #tmp_dates values ('20111001')
insert into #tmp_dates values ('20111101')
insert into #tmp_dates values ('20111201')
insert into #tmp_dates values ('20120101')
insert into #tmp_dates values ('20120201')
insert into #tmp_dates values ('20120301')
insert into #tmp_dates values ('20120401')
insert into #tmp_dates values ('20120501')
insert into #tmp_dates values ('20120601')
insert into #tmp_dates values ('20120701')
insert into #tmp_dates values ('20120801')
insert into #tmp_dates values ('20120901')
insert into #tmp_dates values ('20121001')
insert into #tmp_dates values ('20121101')
insert into #tmp_dates values ('20121201')
insert into #tmp_dates values ('20130101')
insert into #tmp_dates values ('20130201')
insert into #tmp_dates values ('20130301')
insert into #tmp_dates values ('20130401')
insert into #tmp_dates values ('20130501')
insert into #tmp_dates values ('20130601')
insert into #tmp_dates values ('20130701')
insert into #tmp_dates values ('20130801')
insert into #tmp_dates values ('20130901')
insert into #tmp_dates values ('20131001')
insert into #tmp_dates values ('20131101')
insert into #tmp_dates values ('20131201')
insert into #tmp_dates values ('20140101')
insert into #tmp_dates values ('20140201')
insert into #tmp_dates values ('20140301')


SELECT date, answered_questions, total_questions, ratio = (CAST(answered_questions AS float) / NULLIF(total_questions,0))
    FROM (SELECT date, 
       total_questions = (SELECT Count(*) FROM Posts
           WHERE PostTypeId = 1 
           AND ClosedDate IS NULL
           AND CreationDate < date
       ),
       answered_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)
       ) FROM #tmp_dates ) AS t

fork query

edited mar 4 14 blah238

created may 9 11 nletcher