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 (currently 9,115,919 versus 7,631,156 rows).

Lifehacks

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

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results