Question rejection trajectory, tag-filterable

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

DECLARE @Raw TABLE (
  MonthId INT,
  Year INT,
  Month INT,
  AcceptedQuestions INT,
  RejectedQuestions INT
);

WITH Raw1 AS (
  SELECT
    DATEPART(yyyy, CreationDate) AS Year,
    DATEPART(mm, CreationDate) AS Month,
    ClosedDate,
    DeletionDate,
    Score
  FROM PostsWithDeleted
  WHERE
    Tags LIKE '%<' + ##Tag:string## + '>%'
    AND
    PostTypeId=1
    AND
    CreationDate < DATEFROMPARTS(
      DATEPART(yyyy, DATEADD(mm, -1, CURRENT_TIMESTAMP)),
      DATEPART(mm, DATEADD(mm, -1, CURRENT_TIMESTAMP)),
      1
    )
),
Raw2 AS (
  SELECT
    Year,
    Month,
    COUNT(
      CASE
        WHEN ClosedDate IS NOT NULL THEN 1
        WHEN DeletionDate IS NOT NULL THEN 1
        WHEN Score < 0 THEN 1
        ELSE NULL
      END) AS RejectedQuestions,
    COUNT(*) AS AllQuestions
  FROM Raw1
  GROUP BY Year, Month
)
INSERT INTO @Raw
SELECT
  RANK() OVER (ORDER BY Year, Month) AS MonthId,
  Year,
  Month,
  AllQuestions - RejectedQuestions AS AcceptedQuestions,
  RejectedQuestions
FROM Raw2;

DECLARE @Raw4 TABLE (
  AMonthId INT,
  Year INT,
  Month INT,
  BMonthId INT,
  AcceptedQuestions INT,
  RejectedQuestions INT,
  Weight INT
);

INSERT INTO @Raw4
SELECT
  A.MonthId AS AMonthId,
  A.Year,
  A.Month,
  B.MonthId AS BMonthId,
  B.AcceptedQuestions,
  B.RejectedQuestions,
  ##Smooth:int?6## - ABS(A.MonthId - B.MonthId) AS Weight
FROM @Raw A, @Raw B
WHERE ABS(A.MonthId - B.MonthId) < ##Smooth##;

WITH Raw5 AS (
  SELECT
    MIN(Year) AS Year,
    MIN(Month) AS Month,
    1.0 * AVG(AcceptedQuestions * Weight) / AVG(Weight) AS AcceptedQuestions,
    1.0 * AVG(RejectedQuestions * Weight) / AVG(Weight) AS RejectedQuestions
  FROM @Raw4
  GROUP BY AMonthId
),
Raw6 AS (
  SELECT AcceptedQuestions, Year, Month, RejectedQuestions
  FROM Raw5

  UNION

  SELECT AcceptedQuestions, Year + 1 AS Year, 0 AS Month, RejectedQuestions
  FROM Raw5
  WHERE Month = 12
  
  UNION
  
  SELECT 0 AS AcceptedQuestions, 0 AS Year, 0 AS Month, 0 AS RejectedQestions
)
SELECT
  AcceptedQuestions,
  CASE
    WHEN Year = 0 THEN ''
    ELSE CAST(Year AS VARCHAR(MAX))
  END AS Year,
  RejectedQuestions
FROM Raw6
ORDER BY Year, Month

Enter Parameters

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