0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for serious players and enthusiasts of chess

SELECT
--  CreationYear,
  PostTypeId,
  UserRep,
  Status,
  count(*) AS NumPosts,
  CONCAT(count(CASE WHEN (LEN(Tags) > 0) AND (LEN(Text) IS NULL) AND (LEN(Title) IS NULL) THEN 1 ELSE NULL END) * 100 / count(*), '%') AS TagOnlyPercent,
  count(CASE WHEN (LEN(Tags) > 0) AND (LEN(Text) IS NULL) AND (LEN(Title) IS NULL) THEN 1 ELSE NULL END) AS TagOnlyEdits, 
  count(CASE WHEN (LEN(Tags) IS NULL) AND (LEN(Text) > 0) AND (LEN(Title) IS NULL) THEN 1 ELSE NULL END) AS TextOnlyEdits, 
  count(CASE WHEN (LEN(Tags) IS NULL) AND (LEN(Text) IS NULL) AND (LEN(Title) > 0) THEN 1 ELSE NULL END) AS TitleOnlyEdits,
  count(CASE WHEN (LEN(Tags) > 0) AND (LEN(Text) > 0) AND (LEN(Title) IS NULL) THEN 1 ELSE NULL END) AS TagTitleEdits,
  count(CASE WHEN (LEN(Tags) > 0) AND (LEN(Text) IS NULL) AND (LEN(Title) > 0) THEN 1 ELSE NULL END) AS TagTextEdits,
  count(CASE WHEN (LEN(Tags) IS NULL) AND (LEN(Text) > 0) AND (LEN(Title) > 0) THEN 1 ELSE NULL END) AS TitleTextEdits,
  count(CASE WHEN (LEN(Tags) > 0) AND (LEN(Text) > 0) AND (LEN(Title) > 0) THEN 1 ELSE NULL END) AS TagTextTitleEdits
  FROM (
SELECT
  p.PostTypeId,
--  YEAR(se.CreationDate) AS CreationYear, 
  se.Tags,
  se.Text,
  se.Title,
  CASE 
    WHEN u.Reputation BETWEEN 0 AND 199 THEN 'Low'
    WHEN u.Reputation BETWEEN 200 AND 499 THEN 'MidLow'
    WHEN u.Reputation BETWEEN 500 AND 999 THEN 'MidUpp'
    ELSE 'Upper'
  END as UserRep,
  CASE 
    WHEN se.RejectionDate IS NOT NULL THEN 'Rejected'
    WHEN se.ApprovalDate IS NOT NULL THEN 'Approved'
    ELSE 'Pending'
  END as Status
FROM SuggestedEdits se
LEFT JOIN Posts p ON p.Id = se.PostId
LEFT JOIN Users u ON u.Id = se.OwnerUserId
WHERE 
  (p.PostTypeId = 1 OR p.PostTypeId = 2) AND
  YEAR(se.CreationDate) >= 2024
) as DT1
GROUP BY
--  DT1.CreationYear,
  DT1.PostTypeId, 
  DT1.UserRep, 
  DT1.Status 
ORDER BY 
--  CreationYear, 
  PostTypeId, 
  UserRep, 
  Status

fork query

edited oct 11

created oct 11 PeterJames