Who acknowledged my contributions to favorite questions?

1

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

DECLARE @Favorite tinyint = 5;
DECLARE @GoodAnswerScore int = 2;

-- Favorite questions asked by you
SELECT
      Q.Id AS [Post Link]
    , Q.FavoriteCount AS [Favorites]
    , V.UserId AS [User Link]
    , V.CreationDate AS [Favorite Date]
    , NULL AS [Answer Score]
    , NULL AS [Accepted Answer]
FROM Posts Q
    INNER JOIN Votes V
        ON V.VoteTypeId = @Favorite
           AND Q.OwnerUserId = ##UserId:int##
           AND V.UserId <> Q.OwnerUserId
           AND V.PostId = Q.Id
-- PLUS
UNION
-- favorite questions asked by someone other than yourself
-- that include an answer of yours, which is either:
--   1) the accepted answer, or
--   2) the highest voted, with a score of @GoodAnswerScore or greater.
-- Note: favorite count does not include self favorites.
SELECT
      Q.Id AS [Post Link]
    , Q.FavoriteCount AS [Favorites]
    , V.UserId AS [User Link]
    , V.CreationDate AS [Favorite Date]
    , A.Score AS [Answer Score]
    , CASE
          WHEN Q.AcceptedAnswerId IS NOT NULL THEN 'O'
          ELSE 'X'
      END AS [Accepted Answer]
FROM Posts Q
    INNER JOIN Votes V
        ON V.VoteTypeId = @Favorite
           AND V.UserId <> Q.OwnerUserId
           AND V.PostId = Q.Id
    INNER JOIN Posts A
        ON A.OwnerUserId = ##UserId:int##
           AND V.UserId <> A.OwnerUserId
           AND Q.OwnerUserId <> A.OwnerUserId
           AND
           (
               (
                   Q.AcceptedAnswerId IS NOT NULL
                   AND Q.AcceptedAnswerId = A.Id
               )
               OR
               (
                   Q.AcceptedAnswerId IS NULL
                   AND Q.Id = A.ParentId
                   AND A.Score = (
                       SELECT TOP 1 Score
                       FROM Posts
                       WHERE ParentId = Q.Id
                       ORDER BY Score DESC
                   )
                   AND A.Score >= @GoodAnswerScore
               )
           )
ORDER BY
      [Accepted Answer] ASC
    , [Favorites] DESC
    , [Answer Score] DESC
    , Q.Id ASC
    , V.UserId ASC
;

Enter Parameters

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