Unsungedness details of a single user

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

DECLARE @dumpUtcDate DATETIME = (
  SELECT MAX(CreationDate) FROM PostsWithDeleted
);

WITH Raw AS (
  SELECT
    A.Id,
    A.Score,
    A.CreationDate,
    CASE WHEN Q.AcceptedAnswerId = A.Id THEN 'yes' ELSE 'no' END
      AS [Is Accepted?],
    CASE WHEN A.CommunityOwnedDate IS NULL THEN 'no' ELSE 'yes' END
      AS [Is Wiki?],
    CASE WHEN A.OwnerUserId = Q.OwnerUserId THEN 'yes' ELSE 'no' END
      AS [Is Self-Accept?],
    CASE WHEN A.Score = 0 THEN 'yes' ELSE 'no' END
      AS [Is Zero-Score?],
    CASE WHEN A.CreationDate < @dumpUtcDate - 10 THEN 'yes' ELSE 'no' END
      AS [Is 10-Day old?],
    CASE
      WHEN
        A.CommunityOwnedDate IS NULL
        AND
        Q.AcceptedAnswerId = A.Id
        AND
        Q.OwnerUserId <> A.OwnerUserId
        AND
        A.CreationDate < @dumpUtcDate - 10
      THEN 'yes'
      ELSE 'no'
    END AS [CountedToTotal?]
  FROM Posts Q, Posts A
  WHERE
    Q.Id = A.ParentId
    AND
    A.OwnerUserId = ##SiteUserId:int##
)
SELECT
  RANK() OVER (ORDER BY CreationDate, Id) AS Rank,
  Id AS [Post Link],
  Score,
  CreationDate,
  [Is Accepted?],
  [Is Wiki?],
  [Is 10-Day old?],
  [CountedToTotal?],
  [Is Zero-Score?]
FROM Raw
WHERE
  (
    ##HideUncounted:string?yes## <> 'yes'
    OR
    [CountedToTotal?] = 'yes'
  )
  AND
  (
    ##HideNonZeroScores:string?no## = 'no'
    OR
    Score = 0
  )
ORDER BY CreationDate, Id

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results