I would like to get the list of questioners who asked the most questions per tag. Can you please advise me how to write such a query? I do not care about the reputation of questioners, I care about the quantity of questions they ask.
@Martin I've forked your tag answers query here. I joined posttags and tags which due to the business rules no longer need a posttypeid in the where. And I used owneruserid directly instead of the extra join with users.
That does have the caveat that it now groups on null values for those answers where the user has left the site. You win some you lose some. Fixable with a where ...
Probably I could have also omitted the condition on PostTypeId - since ParentId will be NULL if it is not an answer....? (I am not sure whether this makes much of difference.)
The advantage of join with PostTags is that I can simply use = ##tagName## instead of LIKE '%<##tagName##>%'?
@Martin yes, but you have to be careful there as wiki and excerpts and election nominations also lack a parentid. But they don't have tags so in this case it won't matter so you'll be good with that assumption.
@Martin yes. And that has the additional benefit that it will use the index on the tags table. a where clause with Like '%something% will always give you a full table scan.
I tend to look at the execution plan to see if I expect any gains. Reducing/removing Index scans is generally an improvement. On small db's you probably won't notice. For the stackoverflow database it does matter, specially if you're hitting posts(withdeleted)
I have often used SEDE when searching for something in a specific tag. And I have always used Tags LIKE '%<##tagName##>. I'll try to use the PostTags table in the future.
I have edited the post. (It now explicitly mentions the SEDE chatroom - which probably counts as advertising this room. So at least form this POV it was useful.)
Still, sometimes I am searching in PostsWithDeleted. In such cases I cannot use PostTags. (I suppose that the PostTags table does not contain the posts that were deleted.)
I would like to know the reputation score from a certain user from sometimes ago, is there the possibility of recovering data from past with the SQL language?
@Martin yeah, that is indeed a problem. I understand they sanitize tables and I'm happy they introduced PostsWithDeleted. Do note that the tags field was added later as a field to be NOT sanitized. In the initial introduction Tags was null in Postswithdeleted ...
I would like to get the list of questioners who asked the most questions per tag. Can you please advise me how to write such a query? I do not care about the reputation of questioners, I care about the quantity of questions they ask.
I would like to know the reputation score from a certain user from sometimes ago, is there the possibility of recovering data from past with the SQL language?
