# Complex user activity report requirement

**URL:** <https://meta.discourse.org/t/complex-user-activity-report-requirement/198641>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [July 30, 2021, 1:47am UTC](https://meta.discourse.org/t/complex-user-activity-report-requirement/198641 "2021-07-30T01:47:00Z")\
**Posts on this page:** 1\
**Showing post:** 20

<div class="post-metadata">

**Author:** ![Grayden\_Shand](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/grayden_shand/32/166586_2.png) [@Grayden\_Shand](https://meta.discourse.org/u/Grayden_Shand)\
**Post date:** [August 5, 2021, 1:07pm UTC](https://meta.discourse.org/t/complex-user-activity-report-requirement/198641/20 "2021-08-05T13:07:15Z")

</div>

> [@Paul\_King](#):
>
> If I may pick your brains a little more, do the tallies generated by this query include posts by email, and replies by email for users on email list mode? If not, how can I include them?

Yes it should. As David mentioned, the `posts` table contains a `via_email` boolean field. The query currently ignores this field and counts all posts whether they were posted via email or not.

> [@Paul\_King](#):
>
> Also how can I include the value of a custom user profile field beside the user’s name?

There’s a table named `user_custom_fields`. You would need to join on this table to include a particular custom field.

I would probably do it in the `post_summary` sub-query.

e.g.

```plaintext
...
), posts_summary AS (
SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received, cf.value as LABEL_FOR_CUSTOM_FIELD
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and cf.name == "YOUR CUSTOM FIELD NAME") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
...

```

I added a column to the `SELECT` clause, and added a new `JOIN` clause for the user\_custom\_fields table.

Note that you’d need to replace `"YOUR CUSTOM FIELD NAME"` and `LABEL_FOR_CUSTOM_FIELD`.

You’ll then also need to update the columns you’re selecting in the final query.

```plaintext
...
SELECT ps.id, ps.username, ps.created_at, ps.LABEL_FOR_CUSTOM_FIELD, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
..

```

That’s probably how I would approach it.

Good luck!

---

_[View the full topic](https://meta.discourse.org/t/complex-user-activity-report-requirement/198641)._