Protected Questions ordered by Last Activity

1

Please login or register to vote for this query.

(click on this box to dismiss)

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

SELECT
  p.Id AS [Post Link],
  p.CreationDate AS [Post Date],
  MAX(h1.CreationDate) AS [Protection Date],
  p.LastActivityDate AS [Last Activity],
  (
    SELECT COUNT(a.Id)
    FROM Posts a
    WHERE a.ParentId = p.Id
      AND a.PostTypeId = 2
      AND a.DeletionDate IS NULL
      AND a.Score > 0
  ) AS [Positive Answers],
  (
    SELECT COUNT(a.Id)
    FROM Posts a
    WHERE a.ParentId = p.Id
      AND a.PostTypeId = 2
      AND a.DeletionDate IS NULL
      AND a.Score <= 0
  ) AS [Negative Answers],
  (
    SELECT COUNT(a.Id)
    FROM PostsWithDeleted a
    WHERE a.ParentId = p.Id
      AND a.PostTypeId = 2
      AND a.DeletionDate IS NOT NULL
  ) AS [Deleted Answers],
  p.tags
FROM Posts p
JOIN PostHistory h1
  ON (p.Id = h1.PostId AND h1.PostHistoryTypeId = 19)
LEFT JOIN PostHistory h2
  ON (p.Id = h2.PostId AND h2.PostHistoryTypeId = 20 AND h2.CreationDate > h1.CreationDate)
WHERE p.PostTypeId = 1
  AND h2.PostId IS NULL
GROUP BY
  p.Id,
  p.CreationDate,
  p.LastActivityDate,
  p.tags
ORDER BY
  p.LastActivityDate

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results