I'm doing some research for a meta post on Gardening.SE (where I'm a pro tempore moderator). I'm using the Stack Exchange Data Explorer (SEDE) to find information about first time users who post questions in our [identification] tag, and how they subsequently interact with the people who respond to their question, whether that's comments on the post, comments on answers, editing the post to provide more information, and so on.
I have the following query which gives me the list of people with no answers and one question that includes the [identification] tag. I plan to refine it by adding extra criteria, e.g. to find instances where the OP leaves a comment replying to someone else.
-- Find users who have exactly one post
-- with that post being a question
-- and the question has the [identification] tag
SELECT
Users.Id AS [User Link], Posts.Id AS [Post Link]
FROM
Users INNER JOIN Posts ON Posts.OwnerUserId = Users.Id
INNER JOIN PostTags ON Posts.Id = PostTags.PostId
INNER JOIN Tags ON Tags.Id = PostTags.TagId
WHERE
Tags.TagName LIKE 'identification' AND
Posts.ParentId IS NULL AND -- ParentId is NULL if post is a question
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.
My concern is that this SQL is far from optimal. I'm looking especially at the repeated inner joins between the Users and Posts tables, and the similarity between the sub-queries to find users with one question and users with at least one answer.
Gardening.SE is a small site, so this query consistently takes less than 75ms to return its 101 records, but even so I'd like to make it run more efficiently, if possible. As I said, I plan to enhance this query, which will mean pulling in more tables, so I'm also concerned about it scaling that way.
Data Explorer links:
- Users with one [identification] question and no answers — the above query.
- Drive-by users with one [identification] question — a derived query for users who post and are never seen again.