1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

DECLARE @now DATETIME = (SELECT MAX(CreationDate) FROM Posts);

WITH Raw AS (
  SELECT
    Id,
    86400.0 * ViewCount / DATEDIFF(s, CreationDate, @now)
      AS ViewsPerDay,
    CreationDate
  FROM Posts
  WHERE
    PostTypeId = 1
    AND
    Title LIKE '% i %'
    AND (
      (
        ClosedDate < DATEADD(d, -9, @now)
        AND
        Score = 1
        AND
        AcceptedAnswerId IS NULL
        AND (
          LastEditDate IS NULL
          OR
          LastEditDate < DATEADD(d, -9, @now)
        )
        AND
        Id NOT IN (SELECT PostId FROM PostLinks WHERE LinkTypeId = 1)
        AND
        Id NOT IN (
          SELECT Q.Id
          FROM Posts Q, Posts A
          WHERE
            A.ParentId = Q.Id
            AND
            Q.ClosedDate IS NOT NULL
            AND
            A.Score >= 1
        )

      ) OR (

        CreationDate < DATEADD(d, -30, @now)
        AND
        AnswerCount = 0
        AND
        Score = 0

      ) OR (

        CreationDate < DATEADD(d, -365, @now)
        AND
        AnswerCount = 0
        AND
        CommentCount <= 1
        AND
        86400.0 * ViewCount / DATEDIFF(s, CreationDate, @now) <= 1.5
        AND (
          (Score = 1 AND OwnerUserId IS NOT NULL)
          OR
          (Score = 2 AND OwnerUserId IS NULL)
        )
      )
    )
)
SELECT
  RANK() OVER (ORDER BY ViewsPerDay DESC, Id) AS Rank,
  Id AS [Post Link],
  ViewsPerDay,
  CreationDate
FROM Raw /* -- */
ORDER BY ViewsPerDay DESC, Id

fork query

edited jan 24

created jan 7 peterh - PLEASE DELETE THIS ACCOUNT