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

1

Please login or register to vote for this query.

(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.

Gardening and Landscaping Meta

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
    Q.AcceptedAnswerId = A.Id
    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:
loading Hold tight while we fetch your results