SELECT PostTypeId, RepCategory, count(*) AS NumPosts, Tag...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Sustainable Living

Q&A for folks dedicated to a lifestyle that can be maintained indefinitely without depleting available resources

SELECT 
  PostTypeId,
  RepCategory,
  count(*) AS NumPosts,
  TagOnlyEdits,
  TextOnlyEdits,
  TitleOnlyEdits,
  TagTitleEdits,
  TagTextEdits,
  TitleTextEdits,
  TagTextTitleEdits
  FROM (
SELECT
  p.PostTypeId,
  CASE 
    WHEN u.Reputation BETWEEN 0 AND 99 THEN 'NewUser'
    WHEN u.Reputation BETWEEN 100 AND 499 THEN 'LowerRepUser'
    ELSE 'HigherRepUser'
  END as RepCategory,
  count(CASE WHEN (LEN(se.Tags) > 0) AND (LEN(se.Text) IS NULL) AND (LEN(se.Title) IS NULL) THEN 1 ELSE NULL END) AS TagOnlyEdits, 
  count(CASE WHEN (LEN(se.Tags) IS NULL) AND (LEN(se.Text) > 0) AND (LEN(se.Title) IS NULL) THEN 1 ELSE NULL END) AS TextOnlyEdits, 
  count(CASE WHEN (LEN(se.Tags) IS NULL) AND (LEN(se.Text) IS NULL) AND (LEN(se.Title) > 0) THEN 1 ELSE NULL END) AS TitleOnlyEdits,
  count(CASE WHEN (LEN(se.Tags) > 0) AND (LEN(se.Text) > 0) AND (LEN(se.Title) IS NULL) THEN 1 ELSE NULL END) AS TagTitleEdits,
  count(CASE WHEN (LEN(se.Tags) > 0) AND (LEN(se.Text) IS NULL) AND (LEN(se.Title) > 0) THEN 1 ELSE NULL END) AS TagTextEdits,
  count(CASE WHEN (LEN(se.Tags) IS NULL) AND (LEN(se.Text) > 0) AND (LEN(se.Title) > 0) THEN 1 ELSE NULL END) AS TitleTextEdits,
  count(CASE WHEN (LEN(se.Tags) > 0) AND (LEN(se.Text) > 0) AND (LEN(se.Title) > 0) THEN 1 ELSE NULL END) AS TagTextTitleEdits
FROM SuggestedEdits se
LEFT JOIN Posts p ON p.Id = se.PostId
LEFT JOIN Users u ON u.Id = se.OwnerUserId
WHERE 
  se.RejectionDate is NULL 
  AND se.ApprovalDate is NULL 
  GROUP BY p.PostTypeId, u.Reputation) as DT1
GROUP BY 
  DT1.PostTypeId, 
  DT1.RepCategory, 
  DT1.TagOnlyEdits,
  DT1.TextOnlyEdits,
  DT1.TitleOnlyEdits,
  DT1.TagTitleEdits,
  DT1.TagTextEdits,
  DT1.TitleTextEdits,
  DT1.TagTextTitleEdits

Enter Parameters

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