1

Please login or register to vote for this query.

(click on this box to dismiss)

Well done: 1) positive score 2) not closed 3) not deleted 4) has an accepted answer 5) accepted answer has a positive score. SO is too big for this query.

Q&A about the site for people who build and repair bicycles, people who train cycling, or commute on bicycles

DECLARE @now DATETIME = (
  SELECT TOP 1
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
  FROM Posts
  ORDER BY Id DESC
);

WITH TotalQ AS (
  SELECT
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Month,
    COUNT(*) AS CreatedCount
  FROM PostsWithDeleted
  WHERE
    PostTypeId = 1
    AND
    CreationDate >= ##Since:string?2000-01-01##
  GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
),
WellDoneQ AS (
  SELECT
    DATEFROMPARTS(YEAR(Q.CreationDate), MONTH(Q.CreationDate), 1) AS Month,
    COUNT(*) AS WellDoneCount
  FROM PostsWithDeleted Q, PostsWithDeleted A
  WHERE
    Q.AcceptedAnswerId = A.Id
    AND
    Q.ClosedDate IS NULL
    AND
    Q.DeletionDate IS NULL
    AND
    A.DeletionDate IS NULL
    AND
    Q.CreationDate >= ##Since##
    AND
    Q.Score > 0
    AND
    A.Score > 0
  GROUP BY DATEFROMPARTS(YEAR(Q.CreationDate), MONTH(Q.CreationDate), 1)
)
SELECT
  TotalQ.Month,
  CASE
    WHEN ##ShowPercentage:int?0## <> 0
    THEN 100.0 * WellDoneQ.WellDoneCount / TotalQ.CreatedCount
  END AS WellDonePct,
  CASE
    WHEN ##ShowPercentage## = 0
    THEN WellDoneQ.WellDoneCount
  END AS WellDoneCount,
  CASE
    WHEN ##ShowPercentage## = 0
    THEN TotalQ.CreatedCount
  END AS CreatedCount
FROM TotalQ, WellDoneQ
WHERE
  TotalQ.Month = WellDoneQ.Month
  AND
  TotalQ.Month < @now
ORDER BY TotalQ.Month DESC

fork query

edited feb 9

created feb 9 peterh - PLEASE DELETE THIS ACCOUNT