This should probably be a comment in a thread but i couldn’t find the correct one. The cool scripts one seems to be locked now. The script is designed to pull user_id, username, name, email, custom_field1, custom_field2, trust_level, visits and a few more stats. Here’s the code SELECT pr.user_id, username, name, email, uf.custom_field1, uf.custom_field2, tl.trust_level, visits, COALESCE(topics_viewed, 0) AS topics_viewed, COALESCE(posts_read, 0) AS posts_read, COALESCE(posts_created, 0) AS posts_created, COALESCE(topics_created, 0) AS topics_created, COALESCE(topics_with_replies, 0) AS topics_with_replies, COALESCE(likes_given, 0) AS likes_given, COALESCE(likes_received, 0) AS likes_received FROM pr LEFT JOIN tv USING (user_id) LEFT JOIN pc USING (user_id) LEFT JOIN tc USING (user_id) LEFT JOIN twr USING (user_id) LEFT JOIN lg USING (user_id) LEFT JOIN e USING (user_id) LEFT JOIN uf USING (user_id) LEFT JOIN tl USING (user_id) and here’s the error message i’m getting: PG::UndefinedTable: ERROR: relation "pr" does not exist LINE 23: FROM pr ^ Can anyone see what i’ve missed as i just can’t find it. I’m not skilled in SQL so its been a bit of a struggle getting this far. Please and thank you

I think it’s been adapted from this post: [image] User participation Data & reporting User participation Shows the following columns over a specified time range (in days): user_id, username, name, email, visits, topics_viewed, posts_read, posts_created, topics_created, topics_with_replies, likes_given, likes_received Full query: -- [params] -- int :from_days_ago = 0 -- int :duration_days = 30 with t as ( select CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days… But you would need to use the full query @Drew-ART, rather than cherry-picking bits without adapting it to compensate for the missing pieces.

The problem is that none of those tables exist. FROM pr means to select rows from the table pr but there is no table pr in Discourse. Maybe you meant posts or topics ? Ditto for every other join, none of those tables exist.

Created a script but receiving an error message. Would love some feedback/advice

Community Building Data & reporting

Drew-ART (Drew) January 16, 2023, 7:52pm 4

Thank you @Falco and @JammyDodger. Looks like i skipped a big chunk of this. I’ll have another go

Topic		Replies	Views
Attempting to create Monthly Leaderboard query - head exploding Data & reporting sql-query	18	2060	October 8, 2018
Add "named group" as a scope for the "User Participation Statistics" script in data explorer Marketplace	0	739	April 15, 2019
Helpful Data Explorer messages Praise	0	885	October 31, 2022
Relation "selected" does not exist with external Postgres Self-hosting	5	1192	July 24, 2019
How to troubleshoot database errors in an outdated plugin? Development	5	513	February 29, 2024

Created a script but receiving an error message. Would love some feedback/advice

Related topics