CREATE TABLE #mods (UserId int); INSERT INTO #mods VALUES...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Worldbuilding

Q&A for writers/artists using science, geography and culture to construct imaginary worlds and settings

CREATE TABLE #mods (UserId int);
INSERT INTO #mods VALUES (627),(189),(885),(30492),(29),(28),(75),(49);

SELECT Id, PostId AS [Post Link],
  SUM(CASE value WHEN 0 THEN 0 ELSE 1 END) AS '# of mods'
FROM (
SELECT Id, PostId, ca1.value
  FROM PostHistory
  CROSS APPLY (
    SELECT PATINDEX('%"Id":' + CAST(UserId AS NVARCHAR) + ',%', Text)
      AS value
      FROM #mods
  ) ca1
  WHERE PostHistoryTypeId = 10
) AS subquery
GROUP BY Id, PostId
ORDER BY SUM(CASE value WHEN 0 THEN 0 ELSE 1 END) DESC

Enter Parameters

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