A badge for reading time?

data & reporting
1

Hello! I came across the codecademy community and saw that they have 3 badges based on how much time a member spent reading. Does anyone have any idea how we could create a badge like that?

image

So far I made this badge inspired by that Reader badge, but I have no idea what number is this (26 in my case is the maximum number that will show me I’m eligible for the badge).

SELECT user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings
GROUP BY user_id
HAVING count(*) >= 26

These are my stats ( the total reading time I’m interested to pull )

image
image1003×96 3.16 KB

5 Likes
3

This query lists users who spent more than 480 hours reading posts. I hope it helps.

-- millisecond conversion: [qtt hours * 3600000]: 480 * 3600000 = 1,728,000,000

WITH time_reading AS (
    SELECT 
        user_id, 
        SUM(msecs) hours 
    FROM post_timings 
    GROUP BY user_id
    HAVING SUM(msecs) >= 1728000000)

SELECT user_id, CURRENT_TIMESTAMP granted_at
FROM time_reading
3 Likes
4

Your query works fine, so thanks a million times! :slightly_smiling_face:

2 Likes
6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.