0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for SharePoint enthusiasts

WITH UserReps AS (
  SELECT DISTINCT 
    se.OwnerUserId AS Id, 
    u.Reputation AS Reputation,
    CASE
      WHEN u.Reputation BETWEEN 0 AND 99 THEN '0-99'
      WHEN u.Reputation BETWEEN 100 AND 299 THEN '100-299'
      WHEN u.Reputation BETWEEN 300 AND 499 THEN '300-499'
      ELSE '500+' 
    END AS RepRange      
  FROM SuggestedEdits se
    LEFT JOIN Users u ON se.OwnerUserId = u.Id
  WHERE
    Year(se.CreationDate) = 2024
),
PosterUserReps AS (
  SELECT DISTINCT 
    u.Id AS Id, 
    u.Reputation AS Reputation,
    CASE
      WHEN u.Reputation BETWEEN 0 AND 99 THEN '0-99'
      WHEN u.Reputation BETWEEN 100 AND 299 THEN '100-299'
      WHEN u.Reputation BETWEEN 300 AND 499 THEN '300-499'
      ELSE '500+' 
    END AS RepRange      
  FROM SuggestedEdits se
    LEFT JOIN Posts p ON p.Id = se.PostId
    LEFT JOIN Users u ON p.OwnerUserId = u.Id
  WHERE
    Year(se.CreationDate) = 2024
)
SELECT 
--    Month(se.CreationDate),
    ur.RepRange AS 'Editor Rep',
    urp.RepRange AS 'Poster Rep',
    COUNT(*) AS TotalEdits,
    COUNT(CASE WHEN (se.ApprovalDate IS NOT NULL) THEN 1 ELSE NULL END) AS Approved,
    COUNT(CASE WHEN (se.RejectionDate IS NOT NULL) THEN 1 ELSE NULL END) AS Rejected,
    COUNT(CASE WHEN (se.ApprovalDate IS NULL AND se.RejectionDate IS NULL) THEN 1 ELSE NULL END) AS Pending,

    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) > 0) AND (LEN(se.Text) IS NULL) AND (LEN(se.Title) IS NULL) AND (se.RejectionDate IS NOT NULL) THEN 1 ELSE NULL END) AS RejTagOnlyEdits,
    
    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 BodyOnlyEdits,
    COUNT(CASE WHEN (LEN(se.Tags) IS NULL) AND (LEN(se.Text) > 0) AND (LEN(se.Title) IS NULL) AND (se.RejectionDate IS NOT NULL) THEN 1 ELSE NULL END) AS RejBodyOnlyEdits,
    
    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) IS NULL) AND (LEN(se.Text) IS NULL) AND (LEN(se.Title) > 0) AND (se.RejectionDate IS NOT NULL) THEN 1 ELSE NULL END) AS RejTitleOnlyEdits,
    
    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) > 0) AND (LEN(se.Title) IS NULL) AND (se.RejectionDate IS NOT NULL) THEN 1 ELSE NULL END) AS RejTagTitleEdits,

    COUNT(CASE WHEN (LEN(se.Tags) > 0) AND (LEN(se.Text) IS NULL) AND (LEN(se.Title) > 0) THEN 1 ELSE NULL END) AS TagBodyEdits,
    COUNT(CASE WHEN (LEN(se.Tags) > 0) AND (LEN(se.Text) IS NULL) AND (LEN(se.Title) > 0) AND (se.RejectionDate IS NOT NULL) THEN 1 ELSE NULL END) AS RejTagBodyEdits,
    
    COUNT(CASE WHEN (LEN(se.Tags) IS NULL) AND (LEN(se.Text) > 0) AND (LEN(se.Title) > 0) THEN 1 ELSE NULL END) AS TitleBodyEdits,
    COUNT(CASE WHEN (LEN(se.Tags) IS NULL) AND (LEN(se.Text) > 0) AND (LEN(se.Title) > 0) AND (se.RejectionDate IS NOT NULL) THEN 1 ELSE NULL END) AS RejTitleBodyEdits,

    COUNT(CASE WHEN (LEN(se.Tags) > 0) AND (LEN(se.Text) > 0) AND (LEN(se.Title) > 0) THEN 1 ELSE NULL END) AS TagBodyTitleEdits,
    COUNT(CASE WHEN (LEN(se.Tags) > 0) AND (LEN(se.Text) > 0) AND (LEN(se.Title) > 0) AND (se.RejectionDate IS NOT NULL) THEN 1 ELSE NULL END) AS RejTagBodyTitleEdits
  FROM SuggestedEdits se
  LEFT JOIN Posts p ON p.Id = se.PostId
  LEFT JOIN Users u ON (u.Id = se.OwnerUserId)
  LEFT JOIN Users up ON (up.Id = p.OwnerUserId)
  LEFT JOIN Posts pparent ON (pparent.Id = p.ParentId)
  LEFT JOIN UserReps ur ON (ur.Id = se.OwnerUserId)
  LEFT JOIN PosterUserReps urp ON (urp.Id = p.OwnerUserId)
  WHERE 
--    (se.ApprovalDate IS NOT NULL OR se.RejectionDate IS NOT NULL) AND
    p.PostTypeId in (1, 2) AND
    se.CreationDate > ##CreationDate:string?2024-01-01## AND -- Suggested Edit Creation Year 
    (up.Reputation + u.Reputation < ##CombinedRep:int?1000##) AND -- CombinedRep: Combined Editor and Poster Reputation
    (up.Reputation < ##MaxPosterRep:int?200##) -- MaxPosterRep: Maximum Poster Reputation
--    GROUP BY Month(se.CreationDate), ur.RepRange, urp.RepRange
--    ORDER BY Month(se.CreationDate), ur.RepRange, urp.RepRange
    GROUP BY ur.RepRange, urp.RepRange
    ORDER BY ur.RepRange, urp.RepRange

fork query

edited oct 16

created oct 16 PeterJames