Q&A for those who study, teach, research and apply economics and econometrics
/* BEST TIME TO ASK QUESTIONS The best time here is when the delay between a question and an accepted answer is shortest. 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 smallest time-to-answer was for questions asked on Saturday at 1pm GMT. The asker had to wait for only 78 minutes. Axes: - X: Hours, days of week, or both 1.0 is Sunday, 12am GMT; 3.5 means Tuesday, 12pm GMT. - Y: Time-to-answer. The average difference between the time when the question was created and when the accepted answer was created. Parameters: - 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 - MaxTimeDiff: The cut off value for time difference. All time-to-answer above this value will be excluded from the statistics. Default value: 1440 min (24 hours). - UserId: Whose stats to include in calculations. Matters only when All = 0. Values: Your user ID or someone else's. - YearsBack: How far to go back in getting statistics. Default is "1" (year) and data shows that the timerange doesn't matter much. */ DECLARE @All int = ##All:int?1## DECLARE @Frequency int = ##Frequency:int?3## DECLARE @MaxTimeDiff int = ##MaxTimeDiff:int?1440## DECLARE @UsedId int = ##UserId:int## DECLARE @YearsBack int = 1 ;WITH tmp AS ( SELECT TimeDiff = DATEDIFF(minute, q.CreationDate, a.CreationDate), 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 LEFT OUTER JOIN Posts a ON q.AcceptedAnswerId = a.Id WHERE q.AcceptedAnswerId > 0 AND q.OwnerUserId = CASE WHEN @All = 1 THEN q.OwnerUserId ELSE @UsedId END AND q.CreationDate > DATEADD("year", -@YearsBack, CURRENT_TIMESTAMP) ) SELECT Period, ROUND(AVG(CAST(TimeDiff AS FLOAT)), 2) AS [Average time-to-answer, minutes, UTC (GMT)] FROM tmp WHERE ABS(TimeDiff) < @MaxTimeDiff GROUP BY Period ORDER BY Period