Probability of a question to end up "nicely" by month

1

(click on this box to dismiss)

"nicely": positive score, not closed, not deleted. Has an accepted, not deleted, positively-scored answer. Shows only until the last, completely dumped month.

Q&A about the site for gardeners and landscapers

``````DECLARE @TsNow DATETIME;

SELECT @TsNow = DATEFROMPARTS(
DATEPART(yyyy, MAX(CreationDate)),
DATEPART(mm, MAX(CreationDate)),
1)
FROM Posts;

WITH TotalQ AS (
SELECT
DATEFROMPARTS(
DATEPART(yyyy, Q.CreationDate),
DATEPART(mm, Q.CreationDate),
1) AS Month,
COUNT(*) AS Count
FROM PostsWithDeleted Q
WHERE
PostTypeId = 1
AND
CreationDate < @TsNow
GROUP BY
DATEFROMPARTS(
DATEPART(yyyy, Q.CreationDate),
DATEPART(mm, Q.CreationDate),
1)
), NiceQ AS (
SELECT
DATEFROMPARTS(
DATEPART(yyyy, Q.CreationDate),
DATEPART(mm, Q.CreationDate),
1) AS Month,
COUNT(
CASE
WHEN
Q.ClosedDate IS NULL
AND
Q.DeletionDate IS NULL
AND
Q.Score > 0
AND
A.Score > 0
AND
A.DeletionDate IS NULL
THEN 1
END) AS Count
FROM PostsWithDeleted Q, PostsWithDeleted A
WHERE
AND
Q.CreationDate < @TsNow
GROUP BY
DATEFROMPARTS(
DATEPART(yyyy, Q.CreationDate),
DATEPART(mm, Q.CreationDate),
1)
)
SELECT
NiceQ.Month,
CASE
WHEN ##ShowPercentage:int?0## = 0
THEN TotalQ.Count
END AS TotalQ,
CASE
WHEN ##ShowPercentage:int?0## = 0
THEN NiceQ.Count
END AS NiceQ,
CASE
WHEN ##ShowPercentage:int?0## <> 0
THEN 100.0 * NiceQ.Count / TotalQ.Count
END AS PctNice
FROM NiceQ, TotalQ
WHERE NiceQ.Month = TotalQ.Month
ORDER BY Month``````

Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached