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