Mortarboard badges as a function of a user's average commenting hour.

1

Please login or register to vote for this query.

(click on this box to dismiss)

Note that we use the Comments table since there is more data than the posts table (9,115,919 versus 7,631,156 rows).

Stack Overflow

Q&A for professional and enthusiast programmers

WITH UsersPeakHour AS (
    SELECT
        f.UserId,
        --MIN (f.comTime)                             AS minTime,
        --MAX (f.comTime)                             AS maxTime,
        ROUND (AVG (f.comTimeFloat) / 10000.0, 0)   AS avgHour
    FROM (
        SELECT
            c.UserId,
            --c.CreationDate,
            --CAST (c.CreationDate AS TIME(0) )   AS comTime,
            CAST (
                REPLACE (
                    CAST (CAST (c.CreationDate AS TIME(0) ) AS CHAR(8) )
                    , ':', ''
                ) AS FLOAT
            ) AS comTimeFloat
        FROM
            Comments    c
    ) AS f
    GROUP BY
        f.UserId
)
SELECT      u.avgHour                                           AS [User''s Avg Comment hour],
            COUNT (u.UserId)                                    AS [Num Users],
            COUNT (b.ID)                                        AS [Num Mortarboard Badges],
            ROUND (COUNT (b.ID) * 100.0 / COUNT (u.UserId), 2)  AS [Percent of users with badge]
FROM        UsersPeakHour       u
LEFT JOIN   Badges              b
ON          u.UserId            = b.UserId
AND         b.name              = 'Mortarboard'
GROUP BY    u.avgHour
ORDER BY    u.avgHour

Enter Parameters

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