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: Views per question created at time X.
- All: Include all users or 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 = 5
;WITH tmp AS
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 = 1
SELECT Period, CAST(SUM(ViewCount) AS FLOAT)/CAST(COUNT(*) as FLOAT) AS [Views per question]
,COUNT(*) as n
GROUP BY Period
ORDER BY Period