# 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:** 22

<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, 3:32pm UTC](https://meta.discourse.org/t/complex-user-activity-report-requirement/198641/22 "2021-08-05T15:32:40Z")

</div>

I was able to get a running example from your code.

```plaintext
-- coverage: 'week', 'all', or 'date'
-- [params]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), 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 nzrab_number
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 = CONCAT('user_field_', (select id::text from user_fields where name='NZRAB number'))) 
GROUP BY u.id, dr.date, dr.week, cf.value
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, ps.NZRAB_number, 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
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
GROUP BY ps.id, ps.username, ps.created_at, ps.nzrab_number, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END

```

Note, the relationship on `user_custom_fields` is a little weird. If it doesn’t work, you may need to run a separate query to determine the correct name to use:

In a separate query run:

```plaintext
select * from user_custom_fields

```

Find a record in the result set that matches up with your “NZRAB number” field, and look for the name. For me it was “user\_field\_2”.

Once you have that replace this line in the above query:

```plaintext
LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = CONCAT('user_field_', (select id::text from user_fields where name='NZRAB number'))) 

```

with

```plaintext
LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = <YOUR FIELD NAME>)

```

Hopefully you don’t need to do that.

---

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