SEDE - answers that are possibly link only or promotional which were posted by new users

Question

So I have this query where I find answers that may be link only posted by users who were with the site for less than a month. Seems to work just fine.

SELECT
    p.Id AS [Post Link],
    LEN(p.Body) AS BodyLength
FROM
    Posts AS p
INNER JOIN
    Users AS u
ON
    u.Id = p.OwnerUserId
WHERE
    p.PostTypeId = 2
  AND
    p.Score < 3
  AND
    p.Body like '%a href%'
  AND
    LEN(p.Body) < 350
  AND
    p.CreationDate > '2014-07-20T00:00:00.000'
  AND
    u.Reputation < 50
  AND
     DATEDIFF(MONTH, u.CreationDate, GETDATE()) < 1
ORDER BY
    BodyLength ASC

However, I want to select only those answers in which they are answering a question by a new user as well. So where the question asker has only had an account for datediff < 1 month.

I tried some things, but can't figure it out. Would like someone to edit that query to do this, and would be awesome if you could just edit it as slightly as possible so I can see how it works and learn from it too.

Answer 1

I'm by no means an expert at SQL, so there might be a much more efficient way to do this.

That said, I used the Post ParentId field to find the owner of the parent post, then repeated your DATEDIFF clause on that user. This should find answers where both the owner of the answer and the owner of the parent question are accounts created in the past month.

SELECT
    p.Id AS [Post Link],
    LEN(p.Body) AS BodyLength,
    p.ParentId,                             -- this field added for display
    parentUser.DisplayName AS ParentOwner   -- this field added for display
    
FROM
    Posts AS p
INNER JOIN
    Users AS u
ON
    u.Id = p.OwnerUserId
INNER JOIN                          -- this join added to get the parent post
    Posts AS parentPost
ON
    parentPost.Id = p.ParentId
INNER JOIN                          -- this join added to get the parent post owner
    Users AS parentUser
ON
    parentUser.Id = parentPost.OwnerUserId
WHERE
    p.PostTypeId = 2
  AND
    p.Score < 3
  AND
    p.Body like '%a href%'
  AND
    LEN(p.Body) < 350
  AND
    p.CreationDate > '2014-07-20T00:00:00.000'
  AND
    u.Reputation < 50
  AND
     DATEDIFF(MONTH, u.CreationDate, GETDATE()) < 1
  AND
     DATEDIFF(MONTH, parentUser.CreationDate, GETDATE()) < 1
ORDER BY
    BodyLength ASC

I spot checked a few of the results and it seems to be working as expected.

answers that are possibly link only which were posted by new users