Questions Answered within Two Business Days

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

DECLARE @Tag nvarchar(25) = ##Tag:string##;
DECLARE @MinDate datetime = ##MinDate:string##;
DECLARE @MaxDate datetime = ##MaxDate:string##;

WITH QuestionResponses AS (
  SELECT 
    Questions.Id AS QuestionId,
    CONCAT(DATENAME(yy, Questions.CreationDate), 'Q', DATENAME(qq, Questions.CreationDate)) AS Quarter,
    DATEDIFF(dd, Questions.CreationDate, Answers.CreationDate)
      -(DATEDIFF(wk, Questions.CreationDate, Answers.CreationDate) * 2)
      -(CASE WHEN DATENAME(dw, Questions.CreationDate) = 'Sunday' THEN 1 ELSE 0 END)
      -(CASE WHEN DATENAME(dw, Answers.CreationDate) = 'Saturday' THEN 1 ELSE 0 END) AS ResponseDays
  FROM 
    Posts AS Questions
    LEFT JOIN Posts Answers ON (Questions.Id = Answers.ParentId)
    INNER JOIN PostTags ON (Questions.Id = PostTags.PostId)
    INNER JOIN Tags ON (PostTags.TagId = Tags.Id)
    INNER JOIN Votes ON (Questions.Id = Votes.PostId)
  WHERE 
    Questions.PostTypeId = 1 AND
    Tags.TagName = @Tag
  ),
FirstQuestionResponses AS (
  SELECT
    QuestionId,
    Quarter,
    MIN(CASE WHEN ResponseDays < 0 THEN 0 ELSE ResponseDays END) AS ResponseDays
  FROM QuestionResponses
  GROUP BY QuestionId, Quarter
  ),
QuarterSummary AS (
  SELECT
    Quarter,
    COUNT(*) AS Total,
    SUM(CASE WHEN ResponseDays >= 0 AND ResponseDays <= 2 THEN 1 ELSE 0 END) AS WithinTwoBusinessDays
  FROM FirstQuestionResponses
  GROUP BY Quarter
  )
  
SELECT
  Quarter,
  Total,
  WithinTwoBusinessDays,
  FLOOR(WithinTwoBusinessDays * 100 / Total) AS Percentage
FROM QuarterSummary
ORDER BY Quarter;

Enter Parameters

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