# Group Visit Activity
**URL:** https://meta.discourse.org/t/group-visit-activity/275162
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [October 22, 2019, 8:50pm UTC](https://meta.discourse.org/t/group-visit-activity/275162 "2019-10-22T20:50:14Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@dstodgel](https://meta.discourse.org/u/dstodgel)
#### Post date: [October 22, 2019, 8:50pm UTC](https://meta.discourse.org/t/group-visit-activity/275162/1 "2019-10-22T20:50:14Z")
### Group Visit Activity
I have been working on queries that look at activity by groups.
- Unique visitors
- Total Visits
- Mobile versus Desktop Visits
- Percent of Group Membership Visiting
- Avg Read Time per Visit
- Avg Posts Read per Visit
- Avg Read Time per Member
- Avg Posts Read per Member
#### Questions
1. I am suspecting that the `user_visits` table only captures one record per unique user\_id per day given `visited_at` is a DATE and not a DATETIME or TIMESTAMP even if they happen to visit multiple times in a day, from multiple device types. When I run my query by DAY, I see the total unique members are equal to the total number of visits. **Can someone confirm if my assumption is correct?** If it is, the follow-up question becomes **what happens to the value for `mobile` if the user visits three times in a day from a laptop, desktop, then phone** , in any order?
2. Oddly, I also see that the `AVG(uv.posts_read)` is not the same value as `SUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id)`, so I would be curious to hear where my query is making a wrong turn or if I am interpreting the `user_visits` table or fields incorrectly. **Can someone chime in on the difference between those two calculations?**
#### Query:
> **Summary**
>
> ```
> -- [params]
> -- null string :group_name = YourGroupName
> -- date :start_date = 2019/09/01
> -- date :end_date = 2019/10/01
> -- null string :frame = day
>
> with mobile as (
> SELECT uv.id,
> count(DISTINCT(uv.user_id)) as UniqueMobile,
> date_part(:frame, uv.visited_at::date) as Day,
> g.name as GroupName
> from user_visits uv
> join users u on uv.user_id = u.id
> join group_users gu on gu.user_id = u.id
> join groups g on g.id = gu.group_id
> where mobile = true
> and uv.visited_at >= :start_date::date
> and uv.visited_at < :end_date::date
> and g.name = :group_name
> GROUP BY GroupName, Day, uv.id
> ),
>
> desktop as (
> SELECT uv.id,
> count(DISTINCT(uv.user_id)) as UniqueDesktop,
> date_part(:frame, uv.visited_at::date) as Day,
> g.name as GroupName
> from user_visits uv
> join users u on uv.user_id = u.id
> join group_users gu on gu.user_id = u.id
> join groups g on g.id = gu.group_id
> where mobile = false
> and uv.visited_at >= :start_date::date
> and uv.visited_at < :end_date::date
> and g.name = :group_name
> GROUP BY GroupName, Day, uv.id
> )
>
> SELECT
> date_part(:frame, uv.visited_at::date) as VisitDate,
> count(DISTINCT(uv.user_id)) as UniqueMembers,
> count(uv.id) as AllVisits,
> count(m.UniqueMobile) as MobileVisits,
> count(d.UniqueDesktop) as DesktopVisits,
> round((count(DISTINCT(uv.user_id)) * 100.0) / groups.user_count, 2) as Percent,
> round(avg(uv.posts_read),2) as "Posts Read (avg Visit)",
> (interval '1' minute * ROUND((cast(AVG(uv.time_read) as decimal)/60), 2)) as "Read Time (avg Visit)",
> (SUM(uv.posts_read)/count(DISTINCT(uv.user_id))) as "Posts Read (avg Member)",
> date_trunc('second',(interval '1' minute * (ROUND((cast(SUM(uv.time_read) as decimal)/60), 2)/(count(DISTINCT(uv.user_id)))))) as "Read Time (avg Member)"
>
> FROM users
> join group_users on group_users.user_id = users.id
> join groups on group_users.group_id = groups.id
> LEFT join user_visits uv on uv.user_id = users.id
> LEFT JOIN mobile m ON m.id = uv.id
> LEFT JOIN desktop d ON d.id = uv.id
> where groups.name = :group_name
> AND uv.visited_at::date >= :start_date
> and uv.visited_at::date < :end_date
>
> group by VisitDate, groups.user_count
>
> order by VisitDate asc
>
> ```
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [October 22, 2019, 9:36pm UTC](https://meta.discourse.org/t/group-visit-activity/275162/2 "2019-10-22T21:36:59Z")
> [@dstodgel](#):
>
> I am suspecting that the `user_visits` table only captures one record per unique user\_id per day
Yes, this is correct.
> [@dstodgel](#):
>
> **what happens to the value for `mobile` if the user visits three times in a day from a laptop, desktop, then phone** , in any order?
If the user reads posts when they visit from a new device, the `mobile` column will be updated based on the last device that they visited on. For example. If a user starts the day by reading 2 posts on a desktop browser, a `user_visits` entry will be created for the user’s id with `posts_read` set to 2 and `mobile` set to `false`. If the user then logs in on a mobile device and reads 3 more posts, the `user_visits` entry for the day will be updated to `posts_read: 5` and `mobile: true`. This is something you can test with the [Data Explorer](https://meta.discourse.org/t/32566?silent=true), you just have to be sure that the user is reading posts they have not read before.
---
### Author:  [@dstodgel](https://meta.discourse.org/u/dstodgel)
#### Post date: [October 22, 2019, 10:30pm UTC](https://meta.discourse.org/t/group-visit-activity/275162/3 "2019-10-22T22:30:04Z")
Thanks, any ideas on the last question? I am a fan of good data over bad, and that raises eyebrows to be similar and yet not the same, especially given your explanation that there is only one `uv.id` per `uv.user_id` in `user_visits` per `uv.visited_at`.
> [@dstodgel](#):
>
> I also see that the `AVG(uv.posts_read)` is not the same value as `SUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id)`
> [@dstodgel](#):
>
> Can someone chime in on the difference between those two calculations?
It may not be super helpful, but here is a sample from my data:
| VisitDate | Posts Read (avg Visit) | Read (avg Member) |
| --- | --- | --- |
| 13 | 1.18 | 1 |
| 14 | 4.15 | 4 |
| 15 | 7.18 | 7 |
| 16 | 6.15 | 6 |
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [October 22, 2019, 11:05pm UTC](https://meta.discourse.org/t/group-visit-activity/275162/4 "2019-10-22T23:05:08Z")
> [@dstodgel](#):
>
> Can someone chime in on the difference between those two calculations?
When you divide with integers, postgres will return an integer. Try something like `SUM(posts_read)::float / COUNT(DISTINCT(user_id))` and see if you’re still finding a difference. You may need to round the result to two places.