0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

WITH TotalQuestions AS
(
  SELECT
  Tags.Tagname AS Tag,
  YEAR(Questions.CreationDate) as Post_Year,
  Month(Questions.CreationDate) as Post_Month,
  count(Questions.Id) AS Total
  from posts AS Questions
 
    INNER JOIN PostTags 
    ON PostTags.PostId = Questions.Id
    
    INNER JOIN Tags
    on Tags.Id = PostTags.TagId
  WHERE
    Tags.Tagname in ('datastage','ibm-data-replication','ibm-information-server','ibm-governance','watson-knowledge-catalog') 
     GROUP BY
    Tags.Tagname,
   YEAR(Questions.CreationDate),
   Month(Questions.CreationDate)

),
 
 UnansweredScores AS
(
    SELECT
  Tags.Tagname AS Tag,
  YEAR(Unanswered.CreationDate) as Post_Year,
  Month(Unanswered.CreationDate) as Post_Month,
  count(Unanswered.Id) AS unanswered
  from posts AS Unanswered
 
    INNER JOIN PostTags 
    ON PostTags.PostId = Unanswered.Id
    
    INNER JOIN Tags
    on Tags.Id = PostTags.TagId
  WHERE
  Tags.Tagname in ('datastage','ibm-data-replication','ibm-information-server','ibm-governance','watson-knowledge-catalog')  AND
    (Unanswered.AnswerCount < 1 OR  Unanswered.AnswerCount is Null )
     GROUP BY
    Tags.Tagname,
    YEAR(Unanswered.CreationDate),
   Month(Unanswered.CreationDate)
),

 AnswerScores AS
(
    SELECT
  Tags.Tagname AS Tag,
  YEAR(Answered.CreationDate) as Post_Year,
  Month(Answered.CreationDate) as Post_Month,
  count(Answered.Id) AS answered
  from posts AS Answered
 
    INNER JOIN PostTags 
    ON PostTags.PostId = Answered.Id
    
    INNER JOIN Tags
    on Tags.Id = PostTags.TagId
  WHERE
    Tags.Tagname in ('datastage','ibm-data-replication','ibm-information-server','ibm-governance','watson-knowledge-catalog') AND
    Answered.AnswerCount  >0 
     GROUP BY
    Tags.Tagname,
    YEAR(Answered.CreationDate),
   Month(Answered.CreationDate)
)

SELECT  
TotalQuestions.Tag,
TotalQuestions.Post_Year,
TotalQuestions.Post_Month,
TotalQuestions.Total,
UnansweredScores.unanswered,
AnswerScores.answered
from  TotalQuestions
FULL JOIN UnansweredScores 
  ON TotalQuestions.Tag = UnansweredScores.Tag
  And  TotalQuestions.Post_Year= UnansweredScores.Post_Year
  And  TotalQuestions.Post_Month= UnansweredScores.Post_Month
FULL JOIN AnswerScores 
  ON TotalQuestions.Tag = AnswerScores.Tag
  And  TotalQuestions.Post_Year= AnswerScores.Post_Year
  And  TotalQuestions.Post_Month= AnswerScores.Post_Month;

fork query

created aug 26 19 anonymous