Count closed questions with comments suggesting SO or Security

0

Please login or register to vote for this query.

(click on this box to dismiss)

Cryptography

Q&A for software developers, mathematicians and others interested in cryptography

(
  SELECT
    'Stack Overflow' AS [Site],
    Count(*) AS [Commented posts]
  FROM Posts p
  WHERE p.ClosedDate IS NOT NULL
    AND NOT EXISTS (SELECT Id FROM PostHistory h
                    WHERE h.PostId = p.Id
                      AND h.PostHistoryTypeId = 35) -- exclude migrated questions
    AND EXISTS (SELECT TOP 1 Id FROM Comments c
                WHERE c.PostId = p.Id
                  AND (c.Text LIKE '%[Ss]tack[Oo]verflow%' OR
                       c.Text LIKE '%[Ss]tack [Oo]verflow%'))
) UNION (
  SELECT
    'Security' AS [Site],
    Count(*) AS [Commented posts]
  FROM Posts p
  WHERE p.ClosedDate IS NOT NULL
    AND NOT EXISTS (SELECT Id FROM PostHistory h
                    WHERE h.PostId = p.Id
                      AND h.PostHistoryTypeId = 35) -- exclude migrated questions
    AND EXISTS (SELECT TOP 1 Id FROM Comments c
                WHERE c.PostId = p.Id
                  AND (c.Text LIKE '%[Ss]ecurity [Ss]tack%' OR
                       c.Text LIKE '%[Ss]ecurity.[Ss][Ee]%'))
)

Enter Parameters

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