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

0

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

Lifehacks

Q&A for people looking to bypass life's everyday problems with simple tricks

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), 1)  AS [Percent of users with badge],
            COUNT (u.UserId) * 1.0 / ( COUNT (b.Id) + 1 )  AS [hit rate]
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 to meta site
loading Hold tight while we fetch your results