Per-user usage counts of "SEDE" vs "The SEDE"

1

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

SELECT
  MAX(UserId) AS [User Link],
  COUNT(CASE WHEN IsProperNoun = 1 THEN 1 ELSE NULL END) AS SEDE,
  COUNT(CASE WHEN IsProperNoun = 0 THEN 1 ELSE NULL END) AS TheSEDE,
  COUNT(*) AS Total,
  CASE WHEN
    COUNT(CASE WHEN IsProperNoun = 1 THEN 1 ELSE NULL END) >
    COUNT(CASE WHEN IsProperNoun = 0 THEN 1 ELSE NULL END)
  THEN 'SEDE' ELSE 'TheSEDE' END AS Preference
FROM
(
  SELECT
    C.UserId AS UserId,
    CASE WHEN
      C.Text COLLATE Latin1_General_BIN NOT LIKE '%[tT]he SEDE%'
      THEN 1 ELSE 0
    END AS IsProperNoun
  FROM Comments C
  WHERE
    C.Text COLLATE Latin1_General_BIN LIKE '%SEDE%'
  UNION ALL
  SELECT
    P.OwnerUserId AS UserId,
    CASE WHEN
      P.Body COLLATE Latin1_General_BIN NOT LIKE '%[tT]he SEDE%'
      THEN 1 ELSE 0
    END AS IsProperNoun
  FROM Posts P
  WHERE
    P.Body COLLATE Latin1_General_BIN LIKE '%[tT]he SEDE%'
) Counts
GROUP BY UserId
ORDER BY Total DESC
;

Enter Parameters

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