0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to improve their interpersonal communication skills

SELECT 
       'What Have You Tried' Type,
       Count(DISTINCT c.postid)    QuestionCount, 
       Count(DISTINCT a.id)        AnswerCount_ByCommenter, 
       COUNT(DISTINCT p.closeddate)           Closed_Count, 
           Avg(cast (p.answercount as decimal)) Avg_AnswerCount ,

CAST(Count(DISTINCT a.id) as decimal) / CAST(Count(DISTINCT c.postid) as DECIMAL)
Comenter_Answer_Rate


           
FROM   comments c 
       INNER JOIN posts p 
               ON c.postid = p.id 
       LEFT JOIN posts a 
              ON p.id = a.id 
                 AND a.owneruserid = c.userid 
WHERE  p.posttypeid = 1 
       AND c.text LIKE '%WhatHaveYouTried%' 
        OR c.text LIKE '%what have you tried%' 
UNION ALL 
SELECT 
       'Any Comments' Type,
       Count(DISTINCT c.postid)    QuestionCount, 
       Count(DISTINCT a.id)        AnswerCount_ByCommenter, 
       COUNT(DISTINCT p.closeddate) Closed_Count, 
      Avg(cast (p.answercount as decimal))  Avg_AnswerCount ,
      CAST(Count(DISTINCT a.id) as decimal) / CAST(Count(DISTINCT c.postid) as DECIMAL)
Comenter_Answer_Rate
FROM   (SELECT DISTINCT userid, 
                        postid 
        FROM   comments) AS c 
       INNER JOIN posts p 
               ON c.postid = p.id 
       LEFT JOIN posts a 
              ON p.id = a.id 
                 AND a.owneruserid = c.userid 
WHERE  p.posttypeid = 1 
UNION ALL 
SELECT 
       'Any Questions' Type,
       Count(p.id)    QuestionCount, 
       NULL AnswerCount_ByCommenter, 
       COUNT( p.closeddate)          Closed_Count, 
        Avg(cast (p.answercount as decimal)) Avg_AnswerCount ,
       NULL Comenter_Answer_Rate
FROM   
       posts p 
       
WHERE  p.posttypeid = 1

fork query

edited jan 11 13

created jan 11 13 conradfrix