How to avoid users on Stack Overflow

Question

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.)

Answer 1

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