Percentage of Questions by Closed Day

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((q.ClosedDate - q.CreationDate) AS FLOAT)) AS ClosedDays
  FROM PostsWithDeleted q
  WHERE q.PostTypeId = 1
    AND q.ClosedDate IS NOT NULL
  GROUP BY
    q.Id,
    Year(q.CreationDate)
    ),
days AS (
SELECT
  QuestionAskYear,
  CAST(ClosedDays AS INT) - CAST(ClosedDays AS INt) % 1 AS ClosedDays,
  COUNT(*) AS Questions
FROM qs
GROUP BY
  QuestionAskYear,
  CAST(ClosedDays AS INT) - CAST(ClosedDays AS INt) % 1
)
SELECT 
  ClosedDays,
  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
ORDER BY ClosedDays

Enter Parameters

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