0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for music historians, critics, and fans

-- only questions with tags <bash> and <shell>

Select 
  t1.Year,t1.Month,
  t1.Total as 'Number of Questions',
  round(cast(t2.Total_30 as float)/t1.Total*100,2) as '% Accepted < 30',
  round(cast(t3.Total_15 as float)/t1.Total*100,2) as '% Accepted < 15' 
from

(select 
  year(a.CreationDate)  as Year,
  month(a.CreationDate) as Month,
  count(*) as Total
from 
  posts a
where 
  (a.tags like '%<bash>%' or a.tags like '%<shell>%')
group by
  month(a.CreationDate),
  year(a.CreationDate)
) t1

inner join

(select
  count(*) as Total_30,
  month(a.CreationDate) as Month,
  year(a.CreationDate)  as Year
from 
  posts a, posts b
where 
  (a.tags like '%<bash>%' or a.tags like '%<shell>%') and
  (Datediff(minute,a.CreationDate,b.CreationDate) < 30) and
  (a.AcceptedAnswerId = b.id)
group by
  month(a.CreationDate),
  year(a.CreationDate)
) t2

on (t1.Year = t2.Year) and (t1.Month = t2.Month)

inner join

(select
  count(*) as Total_15,
  month(a.CreationDate) as Month,
  year(a.CreationDate)  as Year
from 
  posts a, posts b
where 
  (a.tags like '%<bash>%' or a.tags like '%<shell>%') and
  (Datediff(minute,a.CreationDate,b.CreationDate) < 15) and
  (a.AcceptedAnswerId = b.id)
group by
  month(a.CreationDate),
  year(a.CreationDate)
) t3

on (t2.Year = t3.Year) and (t2.Month = t3.Month)


order by t1.Year,t1.Month

fork query

edited dec 3 13

created oct 2 13 MurielUfu