1

Please login or register to vote for this query.

(click on this box to dismiss)

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

DECLARE
  @UserId1 int = ##UserId1##,
  @UserId2 int = ##UserId2##;

WITH Interesting (InterestingUserId) AS (
  SELECT @UserId1
  UNION ALL SELECT @UserId2
), CandidateComments AS (
  SELECT
    i.InterestingUserId,
    c.PostId
  FROM
    Comments c
    INNER JOIN Interesting i
      ON c.userId = i.InterestingUserId
), CandidatePosts AS (
  SELECT
    i.InterestingUserId,
    QuestionId = Coalesce(q.Id, p.Id),
    AnswerId = CASE WHEN p.PostTypeID = 2 THEN p.Id ELSE NULL END,
    Owned = 1,
    Commented = 0
  FROM
    Posts p
    INNER JOIN Interesting i
      ON p.OwnerUserId = i.InterestingUserId
    LEFT JOIN Posts q
      ON p.ParentId = q.Id
      AND p.PostTypeId = 2
  UNION ALL
  SELECT
    cc.InterestingUserId,
    QuestionId = Coalesce(q.Id, p.Id),
    AnswerId = CASE WHEN p.PostTypeID = 2 THEN p.Id ELSE NULL END,
    Owned = 0,
    Commented = 1
  FROM
    Posts p
    INNER JOIN CandidateComments cc
      ON p.Id = cc.PostId
    LEFT JOIN Posts q
      ON p.ParentId = q.Id
      AND p.PostTypeId = 2
)
SELECT
  cp.QuestionId,
  [Post Link] = cp.QuestionId,
  Question = Max(CASE WHEN cp.AnswerId IS NULL THEN 1 ELSE 0 END),
  Answers = Count(DISTINCT cp.AnswerId),
  Owned = Max(cp.Owned),
  Commented = Max(cp.Commented)
FROM
  CandidatePosts cp
GROUP BY
  cp.QuestionId
HAVING
  Count(DISTINCT cp.InterestingUserId) > 1
;

fork query

edited feb 14 19

created feb 14 19 ErikE