Query Exercise Answer: Beating ChatGPT at Finding Good Question Times
For this week’s Query Exercise, I asked you to write a better query than ChatGPT wrote. Your goal was to find the best days and times to post questions on Stack Overflow.
I found it interesting that a lot of the initial answers focused on the times when there were the most questions, or which questions were the most highly upvoted. For me, the best time to post a question is when you have the highest likelihood of getting the right answer, quickly.
When someone posts a question, they can accept an answer as the right one. You can see it by looking for checkmarks next to an answer. The checkmark indicates that the answer was accepted by the original question-asker.
The accepted answer may not be the best one overall, especially as additional answers come in later over time. However, the accepted answer was good enough for the person who asked the question – and when I’m asking a question, that’s what my goal is, to get an answer that’s good enough to solve my problem, and move on.
In the Posts table where questions & answers are stored, there’s an AcceptedAnswerId column. If a question has an Id in the AcceptedAnswerId, then that’s the Posts.Id for the answer row.
Let’s try this:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT TOP 100 DATENAME(weekday, pQ.CreationDate) AS DayOfWeek, DATEPART(hh, pQ.CreationDate) AS HourOfDay, AVG(DATEDIFF(mi, pQ.CreationDate, pA.CreationDate)) AS AvgMinutesToAnswer, SUM(1) AS QuestionsAnswered FROM dbo.Posts pQ INNER JOIN dbo.Posts pA ON pq.AcceptedAnswerId = pA.Id WHERE pQ.PostTypeId = 1 /* Question, but not really needed */ GROUP BY DATENAME(weekday, pQ.CreationDate), DATEPART(hh,pQ.CreationDate) ORDER BY AVG(DATEDIFF(mi, pQ.CreationDate, pA.CreationDate)), SUM(1) DESC; |
The results in the 2018-06 version of the Stack Overflow database:
It’s looking like weekend mornings are the best times to post questions – but even then, it takes a week to get to a good answer! You might think (or at least I did), “Well, if it takes a whole week, does it even make a difference when I post the question?” Let’s flip the sort order and look for the worst times:
That’s kinda wild – it’s weekday afternoons! (We’ll set time zones aside for this, but that’s a whole ‘nother exercise.) That made me wonder: if we only group the data by day of week, what does it look like?
|
1 2 3 4 5 6 7 8 9 |
SELECT DATENAME(weekday, pQ.CreationDate) AS DayOfWeek, AVG(DATEDIFF(hour, pQ.CreationDate, pA.CreationDate)) AS AvgHoursToAnswer, SUM(1) AS QuestionsAsked, COUNT(DISTINCT pA.Id) AS QuestionsAnswered FROM dbo.Posts pQ LEFT OUTER JOIN dbo.Posts pA ON pq.AcceptedAnswerId = pA.Id WHERE pQ.PostTypeId = 1 /* Question, but not really needed */ GROUP BY DATENAME(weekday, pQ.CreationDate) ORDER BY AVG(DATEDIFF(hour, pQ.CreationDate, pA.CreationDate)) |
I added a couple more columns because the results are pretty conclusive:
Post your questions on the weekends. Sure, there are way less questions coming in at that time – but that’s also when you get more eyeballs on your questions because you have less competition. You’re more likely to get a good answer, faster, when you’re not competing with other questions.
Is the moral of the story that ChatGPT’s answer was bad? No, or at least, no worse than some of the answers us meatbags came up with initially. I think the key to asking a good data question is to keep following up with more questions. What do the query results show? Where do we think the loopholes are? What’s the real business objective that we’re trying to achieve? How do we gauge the accuracy of an answer?
2 Comments. Leave new
[…] Query Exercise Answer: Beating ChatGPT at Finding Good Question Times (Brent Ozar) […]
[…] For this exercise, I’m not expecting a specific “right” or “wrong” answer – instead, for this one, you’re probably going to have a good time sharing your answer in the comments, and comparing your answer to that of others. Feel free to put your queries in a Github Gist, and include that link in your comments. After you’ve worked on it for a while, check out the answers discussion post. […]