When do we add most questions and answers?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/* WHEN DO WE ADD MOST POSTS?
UTC (GMT) time zone. The week starts with Sunday.

Axes:
- X: Hours, days of week, or both
    1.0 is Sunday, 12am GMT; 3.5 means Tuesday, 12pm GMT.
- Y: Number of posts (questions and answers) and of questions alone.

Parameters:
- All: Include all users of only one specified by UserId
    Values: 0 - show stats for one user, 1 - for all users
- HourOrDOW: 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 @Choice int = ##HourOrDOW:int?1##
DECLARE @All int = ##All:int?1##
DECLARE @UsedId int = ##UserId:int##


IF @Choice = 1
  SELECT
    DATEPART("hour", CreationDate) AS [Hour when posts were added],
    COUNT(*) AS [Number of posts per hour, UTC (GMT)],
    COUNT(case PostTypeId when 1 then 1 else null end) AS [Questions]
  FROM Posts
  WHERE OwnerUserId = CASE @All WHEN 1 THEN OwnerUserId ELSE @UsedId END
  GROUP BY DATEPART("hour", CreationDate)
ELSE IF @Choice = 2
  WITH tmp AS
    (
    SELECT
      PostTypeId, OwnerUserId,
      DATEPART("weekday", CreationDate) AS wpart,
      DATENAME(dw, CreationDate) AS wname
    FROM Posts
    )
  SELECT
    wpart AS [Weekday post were added on],
    -- Little hack to show DoW with data.SO graph builder
    -- wname,
    COUNT(*) AS [Number of posts per weekday],
    COUNT(case PostTypeId when 1 then 1 else null end) AS [Questions]
  FROM tmp
  WHERE OwnerUserId = CASE @All WHEN 1 THEN OwnerUserId ELSE @UsedId END
  GROUP BY wpart, wname
  ORDER BY wpart
ELSE IF @Choice = 3
  WITH tmp AS
   (
   SELECT PostTypeId, OwnerUserId,
          ROUND((CAST(DATEPART("weekday", CreationDate) AS float) + CAST(DATEPART("hour", CreationDate) AS float) / 24), 2) AS cd
   FROM Posts
   )
  SELECT
    cd AS [Day of week + time],
    COUNT(*) AS [Number of posts],
    COUNT(case PostTypeId when 1 then 1 else null end) AS [Questions]
  FROM tmp
  WHERE OwnerUserId = CASE @All WHEN 1 THEN OwnerUserId ELSE @UsedId END
  GROUP BY cd
  ORDER BY cd

Enter Parameters

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