Complex user activity report requirement

Data & reporting
21

Thank you so much for that!

The custom user profile field I am trying to use is ‘NZRAB number’

image
image1202×937 64 KB

I had a go, with the resulting script below, but obviously got it wrong, as this gave ‘Undefined function’ error

I was not sure if field name is supposed to exclude capital letters or spaces - the Editor did not seem to like the space in the fieldname at least, so I swapped in an underscore

I might also be conflating the field name with the field label - but if so I am not sure how to tell what field name belongs to the label ‘NZRAB number’

-- 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 == "NZRAB number") 
GROUP BY u.id, dr.date, dr.week
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, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps

LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, 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

resulting error:

PG::UndefinedFunction: ERROR: operator does not exist: integer == integer
LINE 32: LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and c…
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

If you concluded from this that I have not a clue what I am doing, you would be right!

22

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

-- 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:

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:

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

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.

23

Awesome - that worked for me, just as is!

To push my luck, is it technically possible to separately tally posts made that received likes vs those that didn’t?

The reason I ask is that there is otherwise no way to tell whether someone who got 8 likes got them all for a single post, or if they were spread out over several posts

The other question was whether there is a way to constrain output to only those people with NZRAB_number custom field between two user supplied numbers? (cutting out those signups who entered text, or nothing at all for this field)

And to list the user’s real name beside their user id?