"Avg Q & A" Average Scores *TAG SPECIFIC* (Month by month comparison + Graph)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Like this: https://data.stackexchange.com/stackoverflow/query/836890/avg-q-a-average-scores-monthly-month-by-month-comparison-last-10-years#graph but tag-specific.

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

select 
  creationdate,
  posttypeid,
  score,
  case when posttypeid=1 then p.tags else (select pp.tags from posts pp where pp.id=p.parentid) end as [tags]
into #tagPosts from posts p

where
  year(creationdate)>=2009
  and closeddate is null
  and communityowneddate is null 
  and posttypeid in (1,2)

select 
  datefromparts(year(creationdate),month(creationdate),1) as YearMonth,
  
  case when sum(case when posttypeid = 1 then 1 else 0 end) = 0 then 0 else
    round(cast(sum(case when posttypeid = 1 then score else 0 end) as decimal) /
      sum(case when posttypeid = 1 then 1 else 0 end),1) end as [Q],
  case when sum(case when posttypeid = 2 then 1 else 0 end) = 0 then 0 else
    round(cast(sum(case when posttypeid = 2 then score else 0 end) as decimal) /
      sum(case when posttypeid = 2 then 1 else 0 end),1) end as [A]
from #tagposts 
where  tags like '%' + ##queryTag:string?python## +'%'  
group by datefromparts(year(creationdate),month(creationdate),1)
order by datefromparts(year(creationdate),month(creationdate),1)

-- queryTag:Enter a complete or partial tag to query:

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results