Query Exercise: Beat ChatGPT at Finding Good Question Times
What are the best days of the week and times of the day to post a question at StackOverflow.com?
It seems like a simple question, but it’s surprisingly nuanced. I asked ChatGPT’s latest version, 4o, and its answer made me laugh out loud. First off, the T-SQL is terrible: it creates a completely unnecessary temp table. However, a bit of congratulations are in order: at least ChatGPT is trained on the Stack Overflow database schema, and it understood that you have to filter for PostTypeId = 1 for questions (and even put a comment indicating that!)
ChatGPT’s answer assumes that our criteria for a good question is the question’s Score – and I think that’s not a bad assumption to make. After all, a lot of people use Stack Overflow as a game, trying to maximize their reputations. However, let’s assume that we post questions in order to get answers.
I followed up by asking ChatGPT:
What are the best days of the week and times of the day to get good answers quickly?
Again, ChatGPT gets a low score for its generated T-SQL, a high score for understanding the database without additional training, and a flat out failure for botching the query and the results:
So, with that in mind, can you do better than ChatGPT?
Any size version of the Stack Overflow database will work for this exercise. (ChatGPT’s query even fails on the tiny 10GB version.)
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.
14 Comments. Leave new
— What are the best days of the week and times of the day to post a question
— Answer by day of the week and time of the day slot
— CreationDate is saved as UTC
— To clarify further, the CreationDate needs to be converted to
— the time zone of the individual who posted the question
SELECT DayOfTheWeek
, HourOfTheDay
, QuestionCount = COUNT(*)
FROM ( SELECT DayOfTheWeek = DATENAME(dw, CreationDate)
, HourOfTheDay = SUBSTRING(CONVERT(CHAR(8), CreationDate, 108), 1, 2)
FROM dbo.Posts
WHERE PostTypeId = 1
) X
GROUP BY DayOfTheWeek
, HourOfTheDay
ORDER BY QuestionCount DESC
Hmm, so you’re saying the best time to post a question is whenever the most questions are posted?
Why is that? (Genuine question, not being sarcastic.)
The best time to post a question on can vary depending on factors such as the topic of your question, the time zone of the audience you’re targeting, and the overall activity level of the site. If the most questions were posted on that specific day of the week and time slot, I think that is the best time that the audience though too. This question is really on the interpretation of the audience. Can you be more specific on what you want to know. I can also think maybe the best time is when no one is posting.
on when a question got most responses.
Now you’re thinking! After all, what do we post questions for?
Another criterion
— How fast do the question get the first answer
— Answer within a 24 hours period
—
SELECT DayOfTheWeek
, HourOfTheDay
, QuestionsCount = COUNT(*)
, MinutesCount = SUM(X.MinutesCount)
, AvgResponseTimeInMinutes = SUM(X.MinutesCount) / COUNT(*)
FROM ( SELECT DayOfTheWeek = DATENAME(dw, Q.CreationDate)
, HourOfTheDay = SUBSTRING(CONVERT(CHAR(8), Q.CreationDate, 108), 1, 2)
, MinutesCount = CASE WHEN DATEDIFF(minute, Q.CreationDate, A.CreationDate) BETWEEN 1 AND 1440
THEN DATEDIFF(minute, Q.CreationDate, A.CreationDate)
ELSE NULL
END
FROM ( SELECT *
FROM dbo.Posts
WHERE PostTypeId = 1
) Q
JOIN dbo.Posts
A
ON A.Id = Q.AcceptedAnswerId
) X
GROUP BY DayOfTheWeek
, HourOfTheDay
ORDER BY ResponseTimeInMinutes
–Friday 12 68745 5004933 72
–Friday 13 74329 5450884 73
–Friday 11 67849 5145573 75
Hi,
Here is my tentative to answer you quizz using SO 50GB version:
https://gist.github.com/gregy2k/b5b2a607d7e378efa2c60fe7ff18dc93
I also asked chat GPT to generate the original query which required bug fixing and few adjustments to make the result easier to read. Here are the top 3 best question times :
Tuesday: 2PM
Wednesday: 11 PM (late night production bug urgent question :D)
Thursday: 4PM
Greg – okay, good job on cleaning up the query to get rid of the temp table. Now, conceptual question: you’re sorting by the average number of upvotes that the question gets. What made you choose that as the sort order for the best time to ask a question? (I don’t disagree with it, just curious as to your thought process there.)
Since one objective of posting questions is to get a valid solution, I was thinking about finding posts having a higher chance to receive answer which can be considered as more accurate from the votes (in theory) hence the ordering.
The best time to post a question on Stackoverflow.com is in one hour from now.
When you have had time for a coffee, re-worded the question after some more Google.
The reason:
I need to have my problem solved quickly, so I cannot wait until Tuesday afternoon.
But the wording must be super precise, otherwise your question will be down-votes and deleted.
StackOverflow is not as user-friendly as it used to be, so you need to be careful about your status (if you have less that a thousand points)
[…] Query Exercise: Beat ChatGPT at Finding Good Question Times (Brent Ozar) […]
For the first question, using the 10gb db, I got Wednesday 2PM, Wednesday 3PM and Thursday 3PM as the top three times to ask a question. Time of day was a little vague, so I used one hour intervals and counted the answers received for the questions asked in those intervals. Code: https://gist.github.com/cross-apply/29134fa58b39f8f9970a9896136c3a82
I like where you’re going with that! Now, just to get a little deeper: your query results include recent answers on years-old questions. Are you okay with waiting years for answers?
[…] 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 […]