Q&A for Ubuntu users and developers
/* BEST TIME TO ASK QUESTIONS
The best time here is when the probability of having an answer is highest.
On the graph, look for the value on the x-axis that
corresponds to the minimum value on y-axis.
That's the best time to ask questions as defined above.
As for Dec 2013, the biggest probability was for questions asked
on Saturday at 1pm GMT. The asker had to wait for only 78 minutes.
- X: Hours, days of week, or both
1.0 is Sunday, 12am GMT; 3.5 means Tuesday, 12pm GMT.
- Y: Probability of getting an answer. Computed as ratio of questions
that have accepted answers to total questions.
- All: Include all users of only one specified by UserId
Values: 0 - show stats for one user, 1 - for all users
- Frequency: The scale of X-axis
Values: 1 - hours, 2 - day of week, 3 - both
- UserId: Whose stats to include in calculations. Matters only when All = 0.
Values: Your user ID or someone else's.
DECLARE @All int = ##All:int?1##
DECLARE @Frequency int = ##Frequency:int?3##
DECLARE @UsedId int = ##UserId:int##
DECLARE @YearsBack int = 1
;WITH tmp AS
SELECT q.Title, q.AcceptedAnswerId,
Period = CASE @Frequency
WHEN 1 THEN DATEPART("hour", q.CreationDate)
WHEN 2 THEN DATEPART("weekday", q.CreationDate)
WHEN 3 THEN ROUND((CAST(DATEPART("weekday", q.CreationDate) AS numeric) +
CAST(DATEPART("hour", q.CreationDate) AS numeric) / 24), 2) END
FROM Posts q
WHERE q.OwnerUserId = CASE WHEN @All = 1 THEN q.OwnerUserId ELSE @UsedId END
AND q.CreationDate > DATEADD("year", -@YearsBack, CURRENT_TIMESTAMP)
AND q.PostTypeId = 2
--AND q.Title != ''
SELECT Period, CAST(COUNT(case when AcceptedAnswerId > 0 then 1 else null end) AS FLOAT)/CAST(COUNT(*) as FLOAT) AS [Probability of getting an answer]
GROUP BY Period
ORDER BY Period