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.