Anton.Sandbox

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

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.

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

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
- 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
(
  SELECT q.ViewCount,
         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
FROM tmp
GROUP BY Period
ORDER BY Period

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results