1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Salesforce administrators, implementation experts, developers and anybody in-between

/* 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.

Axes:
- 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.

Parameters:
- 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 = 1
    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]
FROM tmp
GROUP BY Period
ORDER BY Period

view current version

edited dec 24 13

created dec 21 13 Anton.