Site suicidal tendencies

1

Please login or register to vote for this query.

(click on this box to dismiss)

This query measures, how the probability of a question to be closed/deleted depends on the number of the questions asked on that day.

Amateur Radio

Q&A for amateur radio enthusiasts

-- (total number of questions in that day )
-- ->
-- P(question posted in that day is closed)

WITH QuestionsPerDay AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      DATEPART(d, CreationDate)) AS CreationDate,
    CASE
      WHEN ClosedDate IS NOT NULL OR DeletionDate IS NOT NULL THEN 1
      ELSE NULL
    END AS Killed
  FROM PostsWithDeleted
  WHERE
    PostTypeId = 1
    AND
    CreationDate > ##FromDate:string?2007-01-01##
    AND
    CreationDate < ##ToDate:string?2018-01-01##
),
DailyStat AS (
  SELECT
    CreationDate,
    COUNT(Killed) AS KilledQ,
    COUNT(*) AS AllQ,
    COUNT(*)/##FragmentSize:int?5## AS Fragment
  FROM QuestionsPerDay
  GROUP BY CreationDate
  HAVING COUNT(*)>0
),
FragmentStat AS (
  SELECT
    Fragment * ##FragmentSize## AS Fragment,
    COUNT(*) AS HowOften,
    CAST(SUM(KilledQ) AS FLOAT)/CAST(SUM(AllQ) AS FLOAT) AS P
  FROM DailyStat
  GROUP BY Fragment
),
TotalDays AS (
  SELECT SUM(HowOften) AS TotalDays
  FROM FragmentStat
),
DoubleHist AS (
  SELECT
    Fragment,
    CAST(HowOften AS FLOAT)/CAST(TotalDays AS FLOAT) AS HowOftenPct,
    P AS 'P(Close|Del)'
  FROM FragmentStat, TotalDays
)
SELECT *
FROM DoubleHist
WHERE HowOftenPct > 0.001
ORDER BY Fragment

Enter Parameters

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