Drive-by users with one [identification] question

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping

Q&A for gardeners and landscapers

-- Find users who have exactly one post
-- with that post being a question
-- and the question has the [identification] tag
-- and the user was never seen again
SELECT
  Users.Id AS [User Link], Posts.Id AS [Post Link], Users.CreationDate AS Joined
FROM
  Users INNER JOIN Posts ON Posts.OwnerUserId = Users.Id
WHERE
  Posts.ParentId IS NULL AND   -- ParentId is NULL if post is a question
  Users.CreationDate = Users.LastAccessDate AND
  Users.Id in (
    -- Users with exactly one question
    SELECT Users.Id
    FROM 
      Users INNER JOIN Posts ON Posts.OwnerUserId = Users.Id
    WHERE
      Posts.ParentId IS NULL
    GROUP BY
      Users.id
    HAVING
      Count(Posts.Id) = 1
    ) AND
  Users.Id NOT IN (
      -- Users with 1 or more answers
    SELECT Users.Id
    FROM 
      Users INNER JOIN Posts ON Posts.OwnerUserId = Users.Id
    WHERE
      Posts.ParentId IS NOT NULL
    GROUP BY
      Users.id
    HAVING
      Count(Posts.Id) > 0
    )
ORDER BY 
  Users.Id DESC  -- newest users first.

Enter Parameters

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