Percentage of changed/retracted/deleted votes per month

1

Please login or register to vote for this query.

(click on this box to dismiss)

It detected all the deleted votes. Changed votes can be seen only if they were changed on a different day.

Stack Overflow

Q&A for professional and enthusiast programmers

WITH Raw AS (
  SELECT
    Id,
    CreationDate,
    LAG(Id) OVER (ORDER BY Id) AS PrevId,
    LAG(CreationDate) OVER (ORDER BY Id) AS PrevCreationDate
  FROM Votes
),
Raw2 AS (
  SELECT
    *,
    CASE
      WHEN CreationDate > PrevCreationDate THEN PrevCreationDate
      ELSE CreationDate
    END AS EstCreationDate
  FROM Raw
)
SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, EstCreationDate),
    DATEPART(m, EstCreationDate),
    1) AS EstMonth,
  100.0 * COUNT(
    CASE WHEN PrevCreationDate > CreationDate OR PrevId + 1 <> Id THEN 1
    ELSE NULL
    END) / COUNT(*) AS ChangedCount
FROM Raw2
GROUP BY DATEFROMPARTS(
  DATEPART(yyyy, EstCreationDate),
  DATEPART(m, EstCreationDate),
  1)
ORDER BY EstMonth

Enter Parameters

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