0

Please login or register to vote for this query.

(click on this box to dismiss)

Homework vs non-homework over time on physics.SE

Q&A for active researchers, academics and students of physics

select CHW+OHW as HW,CHW,OHW,NHW,(CHW+OHW)*100/(CHW+OHW+NHW) as 'PercentHW',OHW*100/(OHW+NHW) as 'PercentOHW', t1.Mo,t1.Yr, concat(t1.yr,'.',t1.mo) as 'Date' from
(SELECT COUNT(*) as 'CHW' ,MONTH(creationdate) as 'Mo' , YEAR(creationdate) as 'Yr' from posts where tags like '%homework%' and closeddate is not null GROUP BY MONTH(creationdate), YEAR(creationdate)) t1
INNER JOIN
(SELECT COUNT(*) as 'NHW' ,MONTH(creationdate) as 'Mo' , YEAR(creationdate) as 'Yr' from posts where tags not like '%homework%' GROUP BY MONTH(creationdate), YEAR(creationdate)) t2
on t1.Mo=t2.Mo and t1.Yr=t2.Yr 
INNER JOIN
(SELECT COUNT(*) as 'OHW' ,MONTH(creationdate) as 'Mo' , YEAR(creationdate) as 'Yr' from posts where tags like '%homework%' and closeddate is null GROUP BY MONTH(creationdate), YEAR(creationdate)) t3
on t1.Mo=t3.Mo and t1.Yr=t3.Yr 
order by t1.yr*100+t1.mo desc

view current version

edited jun 6 13

created jun 5 13 Manishearth