2

Please login or register to vote for this query.

(click on this box to dismiss)

Normalized to 1000 per week. Normalized average: 5.95/hour-of-week, 142/day, 41.67/hour-of-day. The last line shows the number of votes for each day (hourly data is not available), also normalized to 1000 votes/week.

Q&A for anime and manga fans

-- Posts by day of week and hour of day
-- Normalized to 1000 per week.
-- Normalized average: 5.95/hour-of-week, 142/day, 41.67/hour-of-day.
-- The last line shows the number of votes for each day (hourly data is not available),
-- also normalized to 1000 votes/week.

SET DATEFIRST 1
DECLARE @PostsFraction FLOAT
SET @PostsFraction = (SELECT 1000.0 / Count(*) FROM Posts)
DECLARE @VotesFraction FLOAT
SET @VotesFraction = (SELECT 1000.0 / Count(*) FROM Votes)

SELECT
    Str(DatePart(Hour, CreationDate)) As [Hour],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 1 THEN @PostsFraction ELSE 0 END), 4, 2) As [Monday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 2 THEN @PostsFraction ELSE 0 END), 4, 2) As [Tuesday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 3 THEN @PostsFraction ELSE 0 END), 4, 2) As [Wednesday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 4 THEN @PostsFraction ELSE 0 END), 4, 2) As [Thursday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 5 THEN @PostsFraction ELSE 0 END), 4, 2) As [Friday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 6 THEN @PostsFraction ELSE 0 END), 4, 2) As [Saturday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 7 THEN @PostsFraction ELSE 0 END), 4, 2) As [Sunday],
    Str(Count(*) * @PostsFraction, 4, 1) As [Total]
FROM Posts
GROUP BY DatePart(Hour, CreationDate)
--ORDER BY DatePart(Hour, CreationDate)
UNION ALL
SELECT
    'Total' As [Hour],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 1 THEN @PostsFraction ELSE 0 END), 5, 1) As [Monday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 2 THEN @PostsFraction ELSE 0 END), 5, 1) As [Tuesday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 3 THEN @PostsFraction ELSE 0 END), 5, 1) As [Wednesday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 4 THEN @PostsFraction ELSE 0 END), 5, 1) As [Thursday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 5 THEN @PostsFraction ELSE 0 END), 5, 1) As [Friday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 6 THEN @PostsFraction ELSE 0 END), 5, 1) As [Saturday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 7 THEN @PostsFraction ELSE 0 END), 5, 1) As [Sunday],
    Str(Count(*) * @PostsFraction, 5, 0) As [Total]
FROM Posts
UNION ALL
SELECT
    'Votes' As [Hour],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 1 THEN @VotesFraction ELSE 0 END), 5, 1) As [Monday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 2 THEN @VotesFraction ELSE 0 END), 5, 1) As [Tuesday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 3 THEN @VotesFraction ELSE 0 END), 5, 1) As [Wednesday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 4 THEN @VotesFraction ELSE 0 END), 5, 1) As [Thursday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 5 THEN @VotesFraction ELSE 0 END), 5, 1) As [Friday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 6 THEN @VotesFraction ELSE 0 END), 5, 1) As [Saturday],
    Str(Sum(CASE DatePart(Weekday, CreationDate) WHEN 7 THEN @VotesFraction ELSE 0 END), 5, 1) As [Sunday],
    Str(Count(*) * @VotesFraction, 5, 0) As [Total]
FROM Votes

fork query

created nov 1 11 Gilles