0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

select b.Years, b.Groups FirstAnswerGroup,
sum(b.HowManyQuestion) FirstAnswerNoOfQuestions,
--yearlytotal = (select count(*) from posts where AcceptedAnswerId is not null and year(creationdate) = b.Years)
cast(round((sum(b.HowManyQuestion)/cast(44005 as float)*100 ),2) as NUMERIC(15,2)) FirstPercentage
from (
	 select a.Years, 
	case 
	WHEN a.ResponseInDays between  0 and 0 then 00
	WHEN a.ResponseInDays between 1 AND 10 THEN 0110 
										  WHEN a.ResponseInDays between 11 AND 30 THEN 1130
										  WHEN a.ResponseInDays between 31 AND 90 THEN 3190
										  WHEN a.ResponseInDays between 91 AND 180 THEN 91180 
										  ELSE 181000
										  END as Groups, a.ResponseInDays, sum(HowManyQuestion) HowManyQuestion from (

									SELECT datepart(year,question.creationdate) Years,
									DATEDIFF(day, question.creationdate, asnswer.creationdate) ResponseInDays,
									count(*) HowManyQuestion
									FROM posts question
									inner join posts asnswer
									on question.AcceptedAnswerId = asnswer.id
	where datepart(year,question.creationdate) = 2008
									and DATEDIFF(day, question.creationdate, asnswer.creationdate) >= 0
									group by datepart(year,question.creationdate),
									DATEDIFF(day, question.creationdate, asnswer.creationdate)
									) a 
									group by a.Years, a.ResponseInDays
					) b 
					group by b.Years,b.Groups
					order by b.Years,b.Groups

fork query

edited apr 2 19

created jan 8 19 anonymous