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