Have they met?

2

Please login or register to vote for this query.

(click on this box to dismiss)

Where have I 'met' another user? Checks for questions, answers, comments and bounties. Note: detecting comment replies rely on text matching and can include false positives.

Stack Overflow

Q&A for professional and enthusiast programmers

-- Have they met?
-- Where have two users met; answer each other's question, commenting on posts or replying, or both competing on a question
-- May throw up some false positives in reply comments because of @Name matches.

declare @AId int = ##UserAId##;
declare @BId int = ##UserBId##;

declare @AName nvarchar(40)
declare @LikeAName nvarchar(40)
select @AName = DisplayName from Users where Id = @AId
select @LikeAName = '%@' + LOWER(REPLACE(@AName, ' ', '')) + '%'

declare @BName nvarchar(40)
declare @LikeBName nvarchar(40)
select @BName = DisplayName from Users where Id = @BId
select @LikeBName = '%@' + LOWER(REPLACE(@BName, ' ', '')) + '%'

-- Question/Answer meetings
  select
    FORMATMESSAGE('site://q/%d|%s', Questions.Id, Questions.Title) as Link,  
    case
      when Questions.OwnerUserId = @AId then
        FORMATMESSAGE('%s''s question, %s''s answer', @AName, @BName)
      else
        FORMATMESSAGE('%s''s question, %s''s answer', @BName, @AName)
    end as [What],
    case
      when Questions.OwnerUserId = @AId then
        Questions.CreationDate
      else
        Answers.CreationDate
    end as [Date A],
    case
      when Answers.OwnerUserId = @AId then
        Questions.CreationDate
      else
        Answers.CreationDate
    end as [Date B]
  from Posts as Questions
  inner join Posts as Answers
    on Questions.Id = Answers.ParentId
  where Answers.PostTypeId = 2 and Questions.PostTypeId = 1
  and (
        (Questions.OwnerUserId = @AId and Answers.OwnerUserId = @BId)
     or (Questions.OwnerUserid = @BId and Answers.OwnerUserId = @AId)
  )

union

-- Comments on questions
  select 
    FORMATMESSAGE('site://posts/comments/%d|%s', Comment.Id, Question.Title) as Link,  
    case
      when Question.OwnerUserId = @AId then
        FORMATMESSAGE('%s''s question, %s''s comment', @AName, @BName)
      when Question.OwnerUserId = @BId then
        FORMATMESSAGE('%s''s question, %s''s comment', @BName, @AName)
    end as [What],
    case
      when Question.OwnerUserId = @AId then
        Question.CreationDate
      else
        Comment.CreationDate
    end as [Date A],
    case
      when Comment.UserId = @AId then
        Question.CreationDate
      else
        Comment.CreationDate
    end as [Date B]
  from Posts as Question
  inner join Comments as Comment
    on Question.Id = Comment.PostId
  where 
      Question.PostTypeId = 1
  and (
        (Question.OwnerUserId = @AId and Comment.UserId = @BId)
     or (Question.OwnerUserid = @BId and Comment.UserId = @AId)
  )

union

-- Comments on answers
  select 
    FORMATMESSAGE('site://posts/comments/%d|%s', Comment.Id, Question.Title) as Link,  
    case
      when Answer.OwnerUserId = @AId then
        FORMATMESSAGE('%s''s answer, %s''s comment', @AName, @BName)
      when Answer.OwnerUserId = @BId then
        FORMATMESSAGE('%s''s answer, %s''s comment', @BName, @AName)
    end as [What],
    case
      when Answer.OwnerUserId = @AId then
        Answer.CreationDate
      else
        Comment.CreationDate
    end as [Date A],
    case
      when Comment.UserId = @AId then
        Answer.CreationDate
      else
        Comment.CreationDate
    end as [Date B]
  from Posts as Question
  inner join Posts as Answer on Question.id = Answer.ParentId
  inner join Comments as Comment
    on Answer.Id = Comment.PostId
  where 
      Answer.PostTypeId = 2
  and (
        (Answer.OwnerUserId = @AId and Comment.UserId = @BId)
     or (Answer.OwnerUserid = @BId and Comment.UserId = @AId)
  )

union

-- Comments on answers to questions posted
  select 
    FORMATMESSAGE('site://posts/comments/%d|%s', Comment.Id, Question.Title) as Link,  
    case
      when Question.OwnerUserId = @AId then
        FORMATMESSAGE('%s''s question, %s''s comment to one of the answers', @AName, @BName)
      when Question.OwnerUserId = @BId then
        FORMATMESSAGE('%s''s question, %s''s comment to one of the answers', @BName, @AName)
    end as [What],
    case
      when Question.OwnerUserId = @AId then
        Question.CreationDate
      else
        Comment.CreationDate
    end as [Date A],
    case
      when Comment.UserId = @AId then
        Question.CreationDate
      else
        Comment.CreationDate
    end as [Date B]
  from Posts as Question
  inner join Posts as Answer on Question.id = Answer.ParentId
  inner join Comments as Comment
    on Answer.Id = Comment.PostId
  where 
      Answer.PostTypeId = 2
  and (
        (Question.OwnerUserId = @AId and Comment.UserId = @BId)
     or (Question.OwnerUserid = @BId and Comment.UserId = @AId)
  )

union

-- Reply comments
  select
    FORMATMESSAGE('site://posts/comments/%d|%s', Comments.Id, Posts.Title),
    case
      when Comments.UserId = @AId then
        FORMATMESSAGE('%s''s reply to %s''s comment', @AName, @BName)
      when Comments.UserId = @BId then
        FORMATMESSAGE('%s''s reply to %s''s comment', @BName, @AName)
    end as [What],
    -- can't know the date of the comment they addressed
    -- so the following are the date of the post the comment is attached to
    -- and the date of the replying comment
    case
      when Comments.UserId = @AId then
        Comments.CreationDate
      else
        Posts.CreationDate
    end as [Date A],
    case
      when Comments.UserId = @AId then
        Posts.CreationDate
      else
        Comments.CreationDate
    end as [Date B]
  from Comments
    inner join Posts on Comments.PostId = Posts.Id
  where (
       (UserId = @AId and LOWER(Text) like @LikeBName)
    or (UserId = @BId and LOWER(Text) like @LikeAName)
  )
     
union
 -- Both answered the same question
  select
    FORMATMESSAGE('site://q/%d|%s', Question.Id, Question.Title) as Link,  
    'Both answered the same question' as [What],
    Answer1.CreationDate as [Date A],
    Answer2.CreationDate as [Date B]
  from Posts as Question
  inner join Posts as Answer1
   on Question.Id = Answer1.ParentId
  inner join Posts as Answer2
   on Question.Id = Answer2.ParentId
  where
       Question.OwnerUserId NOT IN (@AId, @BId)
   and Answer1.OwnerUserId = @AId
   and Answer2.OwnerUserId = @BId

union

 -- Bounties on questions
  select
    FORMATMESSAGE('site://q/%d|%s', Question.Id, Question.Title) as Link,  
    case
      when Bounty.UserId = @AId then
        FORMATMESSAGE('%s''s %d point bounty on %s''s question', @AName, Bounty.BountyAmount, @BName)
      when Bounty.UserId = @BId then
        FORMATMESSAGE('%s''s %d point bounty on %s''s question', @BName, Bounty.BountyAmount, @AName)
    end as [What],
    Question.CreationDate as [Date A],
    Bounty.CreationDate as [Date B]
  from Posts as Question
  inner join Votes as Bounty
    on Question.id = Bounty.PostId
  where
       Question.PostTypeId = 1
   and Bounty.VoteTypeId = 8
   and (
        (Question.OwnerUserId = @AId and Bounty.UserId = @BId)
     or (Question.OwnerUserid = @BId and Bounty.UserId = @AId)
   )

union

 -- Bounties on questions the other user answered
  select
    FORMATMESSAGE('site://q/%d|%s', Answer.Id, Question.Title) as Link,  
    case
      when Bounty.UserId = @AId then
        FORMATMESSAGE('%s''s %d point bounty on a question that %s answered', @AName, Bounty.BountyAmount, @BName)
      when Bounty.UserId = @BId then
        FORMATMESSAGE('%s''s %d point bounty on a question that %s answered', @BName, Bounty.BountyAmount, @AName)
    end as [What],
    Answer.CreationDate as [Date A],
    Bounty.CreationDate as [Date B]
  from Posts as Question
  inner join Posts as Answer
    on Question.id = Answer.ParentId
  inner join Votes as Bounty
    on Question.id = Bounty.PostId
  where
       Question.PostTypeId = 1
   and Answer.PostTypeId = 2
   and Bounty.VoteTypeId = 8
   -- Bounty should be posted no earlier than 7 days before the answer,
   -- otherwise it would not have been available to award to this answer
   and DateDiff(day, Answer.CreationDate, Bounty.CreationDate) >= -7
   and (
        (Answer.OwnerUserId = @AId and Bounty.UserId = @BId)
     or (Answer.OwnerUserid = @BId and Bounty.UserId = @AId)
   )

union

 -- Bounties awarded to an answer
  select
    FORMATMESSAGE('site://q/%d|%s', Answer.Id, Question.Title) as Link,  
    case
      when Bounty.UserId = @AId then
        FORMATMESSAGE('%s''s %d point bounty awarded to %s''s answer', @AName, Bounty.BountyAmount, @BName)
      when Bounty.UserId = @BId then
        FORMATMESSAGE('%s''s %d point bounty awarded to %s''s answer', @BName, Bounty.BountyAmount, @AName)
    end as [What],
    Answer.CreationDate as [Date A],
    Awarded.CreationDate as [Date B]
  from Posts as Question
  inner join Posts as Answer
    on Question.id = Answer.ParentId
  inner join Votes as Bounty
    on Question.id = Bounty.PostId
  inner join Votes as Awarded
    on Answer.id = Awarded.PostId
  where
       Question.PostTypeId = 1
   and Answer.PostTypeId = 2
   and Bounty.VoteTypeId = 8
   and Awarded.VoteTypeId = 9
   -- Bounty start and award should have taken place within 8 days, and match amounts
   and Bounty.BountyAmount = Awarded.BountyAmount
   and DateDiff(day, Bounty.CreationDate, Awarded.CreationDate) between 0 and 8
   and (
        (Answer.OwnerUserId = @AId and Bounty.UserId = @BId)
     or (Answer.OwnerUserid = @BId and Bounty.UserId = @AId)
   )

order by [Date A], [Date B]

Enter Parameters

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