What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 129 Stack Exchange communities.

(only half serious about this, but it was a fun exercise)

I've got an issue on Stack Overflow. I'd really like to answer some C# questions to hone my skills, but certain users (cough) are way, way better than me, and always beat me to it.

Is there any way I can know when these users are most active, so I can avoid them?

share|improve this question
3  
Interesting question, and good job with the answer! –  Danny Beckett May 3 '13 at 13:14
    
@DannyBeckett Thanks! It started as a joke with a coworker, but then I just had to know. –  Nick Vaccaro May 3 '13 at 13:15
52  
I'm generally asleep between midnight and 6am UK time, if that's any help. –  Jon Skeet May 3 '13 at 13:45
4  
@JonSkeet Ha! Noted. That should help debug. –  Nick Vaccaro May 3 '13 at 13:52
10  
@JonSkeet All I heard was "I have and post the correct answer to all C# questions the world has. And I do it on 6 hours of sleep" –  Jimmy Hoffa May 3 '13 at 17:26
1  
@JonSkeet So you don't actually do any work for your employment, don't eat, don't use the restroom, shower, shave, move, or otherwise interact with any object or human outside of a 6ft radius from your home machine? Or... are you a robot? No wonder you're hard to beat. –  Lee Louviere May 3 '13 at 17:49

1 Answer 1

up vote 34 down vote accepted

Well now there is! Simply edit the display name, and enter this query into the StackExchange Data Explorer (or click here).

DECLARE @DisplayName VARCHAR(200)
SET @DisplayName = 'Jon Skeet'

SELECT CAST(CAST(StartHour AS VARCHAR(2)) + ':' + CAST(StartMinute AS VARCHAR(2)) AS TIME) AS StartTime
  , CAST(CAST(EndHour AS VARCHAR(2)) + ':' + CAST(EndMinute AS VARCHAR(2)) AS TIME) AS EndTime
FROM
(
  SELECT FLOOR(StartTime/100) AS StartHour
    , FLOOR((CAST(FLOOR(StartTime) AS INT)%100) * 60 / 100) AS StartMinute
    , FLOOR(EndTime/100) AS EndHour
    , FLOOR((CAST(FLOOR(EndTime) AS INT)%100) * 60 / 100) AS EndMinute
  FROM
  (
    SELECT AVG(AvgTime) - AVG(StdevTime) AS StartTime
      , AVG(AvgTime) + AVG(StdevTime) AS EndTime
    FROM
    (
      SELECT AVG((DATEPART(HH, c.CreationDate)*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) AS AvgTime
        , STDEV((DATEPART(HH, c.CreationDate)*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) AS StdevTime
        , COUNT(*) AS C
      FROM Users u
      LEFT JOIN Comments c
        ON u.Id = c.UserId
      WHERE u.DisplayName = @DisplayName

        UNION ALL

      SELECT AVG((DATEPART(HH, c.CreationDate)*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) AS AvgTime
        , STDEV((DATEPART(HH, c.CreationDate)*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) AS StdevTime
        , COUNT(*) AS C
      FROM Users u
      LEFT JOIN Posts c
        ON u.Id = c.OwnerUserId
      WHERE u.DisplayName = @DisplayName
    ) a
  ) a
) a

This query grabs the average time and standard deviation of the users posts and comments and gives a most-likely-active window that you should avoid, or not!

Note: I believe the times are UTC.

UPDATE 1:

Based on the above suggestions, I've changed the input variable to the UserId (thanks Martin) and the input to query string parameterization (thanks hammar).

I've also taken a first try at doing a UTC time shift to better determine when a user is most active. Since it's quite likely that any given user is most active during a time period which crosses over UTC midnight, the averages and STDEVs would get all messed up.

I've crossed joined the data with 24 1-hour time shifts and ordered by STDEV ascending, since I think that the min standard deviation is the best result. Shows how well I payed attention in stats class.

Here's the example using my UserId.

And here's the code, for redundancy:

DECLARE @UserId int = ##UserId##

; WITH TimeZoneShifts AS
(
    SELECT 0 AS Shift UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
    SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
    SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
)
, Times AS 
(
  SELECT AvgTime = AVG(AvgTime)
    , StdevTime = AVG(StdevTime)
    , C = SUM(C)
    , Shift
  FROM
  (
      SELECT AvgTime = AVG(((DATEPART(HH, c.CreationDate)+t.Shift)%24*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) 
        , StdevTime = STDEV(((DATEPART(HH, c.CreationDate)+t.Shift)%24*100) + (DATEPART(mi, c.CreationDate) * 100 / 60))
        , C = COUNT(*) 
        , t.Shift
      FROM Users u
      CROSS JOIN TimeZoneShifts t
      LEFT JOIN Comments c
        ON u.Id = c.UserId
      WHERE u.Id = @UserId
      GROUP BY t.Shift

        UNION ALL

      SELECT AvgTime = AVG(((DATEPART(HH, c.CreationDate)+t.Shift)%24*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) 
        , StdevTime = STDEV(((DATEPART(HH, c.CreationDate)+t.Shift)%24*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) 
        , C = COUNT(*) 
        , t.Shift
      FROM Users u
      CROSS JOIN TimeZoneShifts t
      LEFT JOIN Posts c
        ON u.Id = c.OwnerUserId
      WHERE u.Id = @UserId
      GROUP BY t.Shift
  ) a
  GROUP BY Shift
)
SELECT StartTime = CAST(CAST((CAST(StartHour+24-Shift AS INT)%24) AS VARCHAR(2)) + ':' + CAST(StartMinute AS VARCHAR(2)) AS TIME) 
  , EndTime = CAST(CAST((CAST(EndHour+24-Shift AS INT)%24) AS VARCHAR(2)) + ':' + CAST(EndMinute AS VARCHAR(2)) AS TIME) 
  , Shift
  , StdevTime
FROM
(
  SELECT StartHour = FLOOR(StartTime/100) 
    , StartMinute = FLOOR((CAST(FLOOR(StartTime) AS INT)%100) * 60 / 100) 
    , EndHour = FLOOR(EndTime/100) 
    , EndMinute = FLOOR((CAST(FLOOR(EndTime) AS INT)%100) * 60 / 100) 
    , Shift
    , StdevTime
  FROM
  (
    SELECT StartTime = AVG(AvgTime) - AVG(StdevTime) 
      , EndTime = AVG(AvgTime) + AVG(StdevTime) 
      , StdevTime
      , Shift
    FROM Times
    GROUP BY Shift, StdevTime
  ) a
) a
ORDER BY StdevTime ASC
share|improve this answer
34  
There is a bug in your query. It seems to suggest Jon Skeet has an off-time, which is obviously wrong. –  Bart May 3 '13 at 13:21
6  
Another (actual!) bug. You seem to asssume display names are unique. This is not the case. Amended Version –  Martin Smith May 3 '13 at 13:29
4  
You should parameterize the query so that people can enter the name/userid in a box instead of having to fork the query. –  hammar May 3 '13 at 13:31
    
I think I found a bug. If the user is most active during a time period which overlaps midnight, the standard deviation and averages will be all screwed up. Must enhance! –  Nick Vaccaro May 3 '13 at 13:51
1  
Or, you could just search for unanswered posts in your tag and go to the last page of that search. It means the question is either a) not good or b) very hard. If it's the latter, it generally means you have a good chance of answering it without anyone else doing so, but also, you might not get much rep. –  casperOne May 3 '13 at 13:53
7  
@casperOne I've tried that with SQL Server. It's a deep, dark place where all hope to answer questions is but a fevered dream of the madman. –  Nick Vaccaro May 3 '13 at 13:55
    
@casperOne Thought about it again, you are correct. Changes were significant enough to warrant another answer. Apologies. –  Nick Vaccaro May 3 '13 at 16:42
2  
Wow, you spent a lot of time on this! Reputation is your meth, huh? –  regretoverflow May 3 '13 at 16:57
    
I tried putting my user ID, 200291, into the query, and I got an error: “Conversion failed when converting date and/or time from character string.” Any idea why it's failing? –  icktoofay Dec 22 '13 at 22:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .