Percentage of Questions by AnswerDays

0

Please login or register to vote for this query.

(click on this box to dismiss)

GIS

Q&A for cartographers, geographers and GIS professionals

WITH qs AS (
  SELECT
    Year(q.CreationDate) AS QuestionAskYear,
    MIN(CAST((a.CreationDate - q.CreationDate) AS FLOAT)) AS AnsweredDays
  FROM Posts q
  JOIN Posts a ON a.ParentId = q.Id AND a.PostTypeId = 2
  WHERE q.PostTypeId = 1
  GROUP BY
    q.Id,
    Year(q.CreationDate)
    ),
days AS (
SELECT
  QuestionAskYear,
  CAST(AnsweredDays AS INT) - CAST(ANSWEREDdAYS AS INt) % 1 AS AnsweredDays,
  COUNT(*) AS Questions
FROM qs
GROUP BY
  QuestionAskYear,
  CAST(AnsweredDays AS INT) - CAST(ANSWEREDdAYS AS INt) % 1
)
SELECT 
  AnsweredDays,
  FORMAT("2011" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2011) AS FLOAT),'0.00%') AS "2011",
  FORMAT("2012" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2012) AS FLOAT),'0.00%') AS "2012",
  FORMAT("2013" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2013) AS FLOAT),'0.00%') AS "2013",
  FORMAT("2014" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2014) AS FLOAT),'0.00%') AS "2014",
  FORMAT("2015" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2015) AS FLOAT),'0.00%') AS "2015",
  FORMAT("2016" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2016) AS FLOAT),'0.00%') AS "2016",
  FORMAT("2017" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2017) AS FLOAT),'0.00%') AS "2017",
  FORMAT("2018" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2018) AS FLOAT),'0.00%') AS "2018",
  FORMAT("2019" / Cast((SELECT COUNT(qs.QuestionAskYear) FROM qs WHERE qs.QuestionAskYear=2019) AS FLOAT),'0.00%') AS "2019"
FROM days
PIVOT (SUM(Questions) FOR QuestionAskYear IN ("2011","2012","2013","2014","2015","2016","2017","2018","2019")) AS p
WHERE AnsweredDays >=0 AND AnsweredDays < 1000
ORDER BY AnsweredDays

Enter Parameters

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