Amount of time spent reading by Discourse groups

[angus (Angus McLeod)]

I took a look at this for @tobiaseigen and came up with the following:

SELECT group_users.group_id, ROUND((cast(sum(user_visits.time_read) as decimal)/60), 2) FROM user_visits
INNER JOIN group_users ON group_users.user_id = user_visits.user_id
WHERE user_visits.visited_at >= current_date - interval '60' day
GROUP BY group_users.group_id

1. We want each group compared against their time_read sum

2. We need to join group_users to user_visits to make the comparison

3. We want to restrict the records to the last 60 days (for the recent time read)

4. We want to group the results by group_id

If you want to get the results in hh:mm:ss format, just add interval '1' minute * before ROUND.

e.g.