Site zombies

0

Please login or register to vote for this query.

(click on this box to dismiss)

Robotics Meta

Q&A about the site for professional robotic engineers, hobbyists, researchers and students

DECLARE @chunkSize AS int = ##ChunkSize:int?30##
DECLARE @chunks AS int = ##Chunks:int?12##
DECLARE @zombletAge AS int = ##zombletAge:int?6##
;

SELECT
  ROW_NUMBER() OVER (ORDER BY Id Asc) AS Chunk
INTO #PreChunks
FROM Posts
;

WITH Chunks AS (
  SELECT
    DateAdd(weekday, (-c.Chunk + 0) * @chunkSize, Convert(Date, CURRENT_TIMESTAMP)) AS ChunkStart,
    DateAdd(weekday, (-c.Chunk + 1) * @chunkSize, Convert(Date, CURRENT_TIMESTAMP)) AS ChunkEnd,
    @chunks - c.Chunk AS ChunkId
  FROM #PreChunks c
  WHERE c.Chunk <= @chunks

), OwnedPosts AS (
  SELECT
    *
  FROM Posts
  WHERE CommunityOwnedDate is null
    AND ClosedDate is null

), VotesOnAnswers AS (
  -- Record Votes
  SELECT
    p.Id,
    p.ParentId,
    Convert(Date, q.CreationDate) AS OrigCreationDate,
    v.CreationDate,
    case when v.VoteTypeId = 2 then count(*) else -count(*) end as Score
  FROM OwnedPosts p, Votes v, OwnedPosts q
  WHERE v.VoteTypeId in (2, 3)
    AND p.PostTypeId = 2
    AND p.Id = v.PostId
    AND p.ParentId = q.Id
  GROUP BY p.Id, p.ParentId, v.VoteTypeId, v.CreationDate, q.CreationDate

), AnswerMetadata AS (
    -- Record Votes
    SELECT
      Id,
      ParentId,
      OrigCreationDate,
      CreationDate,
      sum(Score) OVER(PARTITION BY Id ORDER BY CreationDate ROWS UNBOUNDED PRECEDING) AS Score
    FROM VotesOnAnswers
  UNION
    -- Record answer creation
    SELECT
      p.Id,
      p.ParentId,
      Convert(Date, q.CreationDate) AS OrigCreationDate,
      Convert(Date, p.CreationDate),
      0 AS Score
    FROM OwnedPosts p, OwnedPosts q
    WHERE p.PostTypeId = 2
      AND p.ParentId = q.Id
    GROUP BY p.Id, p.ParentId, p.CreationDate, q.CreationDate
  UNION
    -- Inital creation
    SELECT
      null,
      Id,
      Convert(Date, CreationDate) AS OrigCreationDate,
      Convert(Date, CreationDate),
      0 AS Score
    FROM OwnedPosts p
    WHERE PostTypeId = 1
  UNION
    -- When the question turns into a zombie from a zomblet
    SELECT
      null,
      Id,
      Convert(Date, CreationDate) AS OrigCreationDate,
      DateAdd(month, @zombletAge, Convert(Date, CreationDate)),
      0 AS Score
    FROM OwnedPosts
    WHERE PostTypeId = 1

), AnswerScores AS (
  SELECT DISTINCT
    a.ParentId,
    a.OrigCreationDate,
    a.CreationDate,
    count(DISTINCT s.Id) as Answers,
    max(s.Score) as Score
  FROM AnswerMetadata a, AnswerMetadata s
  WHERE s.CreationDate <= a.CreationDate
    AND s.ParentId = a.ParentId
  GROUP BY a.ParentId, a.OrigCreationDate, a.CreationDate

), ZombieMetadata AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY ParentId, CreationDate) AS ZombieId,
      ParentId,
      CreationDate,
      case when Answers > 0 then 1 else 0 end as Answered,
      case when CreationDate < DateAdd(month, @zombletAge, Convert(Date, OrigCreationDate)) then 1 else 0 end AS Zomblet,
      case when Score >= 1 then 1 else 0 end AS Killed
    FROM AnswerScores

), Zombies AS (
  SELECT
    ZombieId,
    ParentId,
    CreationDate,
    Killed,
    case when Killed = 1 or Zomblet = 0 then 0 else 1 - Answered end AS Zomblet,
    case when Killed = 1 or Zomblet = 0 then 0 else Answered end AS AnsweredZomblet,
    case when Killed = 1 or Zomblet = 1 then 0 else 1 - Answered end AS Zombie,
    case when Killed = 1 or Zomblet = 1 then 0 else Answered end AS AnsweredZombie
  FROM ZombieMetadata

), DoubleZombies AS (
    SELECT
      *
    FROM Zombies
  UNION
    -- Invert the previous value allowing for a simple cumulative sum
    SELECT
      -curr.ZombieId,
      curr.ParentId,
      next.CreationDate,
      -curr.Killed,
      -curr.Zomblet,
      -curr.AnsweredZomblet,
      -curr.Zombie,
      -curr.AnsweredZombie
    FROM Zombies curr, Zombies next
    WHERE curr.ZombieId = next.ZombieId - 1
      AND curr.ParentId = next.ParentId
    GROUP BY curr.ParentId, next.CreationDate, curr.Killed, curr.Zomblet, curr.AnsweredZomblet, curr.Zombie, curr.AnsweredZombie, curr.ZombieId

), GraphDeltas AS (
  SELECT
    c.ChunkEnd,
    sum(z.Killed) AS Killed,
    sum(z.Zomblet) AS Zomblet,
    sum(z.AnsweredZomblet) AS AnsweredZomblet,
    sum(z.Zombie) AS Zombie,
    sum(z.AnsweredZombie) AS AnsweredZombie
  FROM DoubleZombies z, Chunks c
  WHERE c.ChunkStart < z.CreationDate AND z.CreationDate <= c.ChunkEnd
  GROUP BY c.ChunkEnd

), Graph AS (
  SELECT
    max(ChunkEnd) OVER(ORDER BY ChunkEnd ROWS UNBOUNDED PRECEDING) AS ChunkEnd,
    sum(Killed) OVER(ORDER BY ChunkEnd ROWS UNBOUNDED PRECEDING) AS Killed,
    sum(Zomblet) OVER(ORDER BY ChunkEnd ROWS UNBOUNDED PRECEDING) AS Zomblet,
    sum(AnsweredZomblet) OVER(ORDER BY ChunkEnd ROWS UNBOUNDED PRECEDING) AS AnsweredZomblet,
    sum(Zombie) OVER(ORDER BY ChunkEnd ROWS UNBOUNDED PRECEDING) AS Zombie,
    sum(AnsweredZombie) OVER(ORDER BY ChunkEnd ROWS UNBOUNDED PRECEDING) AS AnsweredZombie
  FROM GraphDeltas
)
SELECT
  ChunkEnd,
  Zomblet,
  AnsweredZomblet,
  Zombie,
  AnsweredZombie,
  Killed / 10 as "Killed / 10"
FROM Graph
ORDER BY ChunkEnd

Enter Parameters

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