Percentage of questions initially posted with a title having the substring " i "

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

WITH Raw AS (
  SELECT
    DATEPART(yyyy, CreationDate) AS year,
    DATEPART(mm, CreationDate) AS month,
    'initial' AS type,
    COUNT(CASE WHEN Text LIKE '% i %' THEN 1 ELSE NULL END) AS iCount,
    COUNT(CASE WHEN
      Text LIKE '% I %' AND NOT Text LIKE '% i %' THEN 1 ELSE NULL END
      ) AS Count
  FROM PostHistory
  WHERE
    PostHistoryTypeId = 1
  GROUP BY DATEPART(yyyy, CreationDate), DATEPART(mm, CreationDate)
  
  UNION

  SELECT
    DATEPART(yyyy, CreationDate) AS year,
    DATEPART(mm, CreationDate) AS month,
    'current' AS type,
    COUNT(CASE WHEN Title LIKE '% i %' THEN 1 ELSE NULL END) AS iCount,
    COUNT(
      CASE
        WHEN Title LIKE '% I %' AND NOT Title LIKE '% i %' THEN 1
      ELSE NULL END
      ) AS Count
  FROM Posts
  WHERE
    PostTypeId = 1
  GROUP BY DATEPART(yyyy, CreationDate), DATEPART(mm, CreationDate)
)
SELECT
  DATEFROMPARTS(year, month, 1) AS month,
  type,
  100.0 * iCount / Count AS iPct
FROM Raw
WHERE Count > 0
ORDER BY month DESC, type

Enter Parameters

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