I have created the following query that can select the top 10 tags that don't have answers today.
It uses a PIVOT query.
Unfortunately I can't / haven't found an easy/nice way to make the columns dynamic based (actually I know how to do it but I'm lazy) on the first tags query. So instead I have a second helper query that produces the needed columns for me that I can then copy/paste in the actual query. I have indicated where the copy paste needs to take place.
To have two series for the same tag (total and unanswered sum) I have used a union to produces a source query that has all the data in it. SQL Server does the grouping, creates the columns based and sums the results.
Here is the source:
-- which tags habe question with 0 answers
;with TagsNoAnswers as
(
select top 10
tagname
, count(*) [q-cnt]
from posts q
inner join posttags pt on pt.postid = q.id
inner join tags t on t.id = pt.tagid
where q.posttypeid = 1 -- Questions
and q.answercount = 0
group by tagname
order by count(*) desc
)
/*
-- for debug
select *
from tagsnoanswers
*/
SELECT [month]
-- copy paste columns here
, [r], [r unanswered]
, [regression], [regression unanswered]
, [machine-learning], [machine-learning unanswered]
, [time-series], [time-series unanswered]
, [hypothesis-testing], [hypothesis-testing unanswered]
, [probability], [probability unanswered]
, [distributions], [distributions unanswered]
, [logistic], [logistic unanswered]
, [self-study], [self-study unanswered]
, [classification], [classification unanswered]
-- end copy paste
FROM
(select eomonth(p.creationdate) [month]
, t.tagname
, count(*) [q-cnt]
from posts p
inner join posttags pt on pt.postid = p.id
inner join tags t on t.id = pt.tagid
inner join tagsnoanswers tn on tn.tagname = t.tagname
where creationdate <= eomonth(dateadd(m,-1,(select max(creationdate) from posts)))
group by eomonth(p.creationdate)
, t.tagname
union
select eomonth(p.creationdate) [month]
, concat(t.tagname, ' ', 'unanswered')
, count(*) [q-cnt]
from posts p
inner join posttags pt on pt.postid = p.id
inner join tags t on t.id = pt.tagid
inner join tagsnoanswers tn on tn.tagname = t.tagname
where creationdate <= eomonth(dateadd(m,-1,(select max(creationdate) from posts)))
and p.answercount = 0
group by eomonth(p.creationdate)
, t.tagname
)
AS src
PIVOT
(
sum([q-cnt])
FOR
tagname
IN (
-- copy paste columns here (remove first , )
[r], [r unanswered]
, [regression], [regression unanswered]
, [machine-learning], [machine-learning unanswered]
, [time-series], [time-series unanswered]
, [hypothesis-testing], [hypothesis-testing unanswered]
, [probability], [probability unanswered]
, [distributions], [distributions unanswered]
, [logistic], [logistic unanswered]
, [self-study], [self-study unanswered]
, [classification], [classification unanswered]
-- end copy-paste
)
) AS pvt
order by month
-- this query generates the columns you need to copy paste in the actual query
select top 10
concat(', [', tagname, '], [', tagname, ' unanswered]')
-- , count(*) [q-cnt]
from posts q
inner join posttags pt on pt.postid = q.id
inner join tags t on t.id = pt.tagid
where q.posttypeid = 1 -- Questions
and q.answercount = 0
group by tagname
order by count(*) desc
SEDE has an awesome tutorial which is definitely worth following, even if you have no SQL experience whatsoever.
The data in SEDE is updated once a week, on Sunday morning.
