50

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 a way I can know when these users are most active, so I can avoid them?

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

6
  • 3
    Interesting question, and good job with the answer! May 3, 2013 at 13:14
  • @DannyBeckett Thanks! It started as a joke with a coworker, but then I just had to know. May 3, 2013 at 13:15
  • 54
    I'm generally asleep between midnight and 6am UK time, if that's any help.
    – Jon Skeet
    May 3, 2013 at 13:45
  • 4
    @JonSkeet Ha! Noted. That should help debug. May 3, 2013 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" May 3, 2013 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. May 3, 2013 at 17:49

1 Answer 1

36

Well, now there is! Simply edit the display name, and enter this query into the Stack Exchange 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
9
  • 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, 2013 at 13:21
  • 6
    Another (actual!) bug. You seem to asssume display names are unique. This is not the case. Amended Version May 3, 2013 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, 2013 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! May 3, 2013 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, 2013 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. May 3, 2013 at 13:55
  • @casperOne Thought about it again, you are correct. Changes were significant enough to warrant another answer. Apologies. May 3, 2013 at 16:42
  • 2
    Wow, you spent a lot of time on this! Reputation is your meth, huh? May 3, 2013 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, 2013 at 22:39

You must log in to answer this question.

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