Excellent, thanks. I really appreciate it!
I also changed this " HAVING count(p.id) > 5" to get results that were more relevant for my community. I’ll keep tinkering with that. It seems that our moderator team is intervening with people before they have posted too often if their posts are low quality. It looks like this query can help validate my sense about someone and also give an early read on when someone isn’t starting off well.
Facebook vs Twitter vs Google vs Github
WITH target_user_ids AS (
SELECT
id
FROM users
WHERE staged = false
AND active = true
AND last_seen_at IS NOT NULL
)
SELECT (
SELECT Count(DISTINCT user_id)
FROM user_associated_accounts
WHERE provider_name = 'google_oauth2'
AND user_id IN(SELECT id FROM target_user_ids)) AS google,
(
SELECT Count(DISTINCT user_id)
FROM user_associated_accounts
WHERE provider_name = 'facebook'
AND user_id IN(SELECT id FROM target_user_ids)) AS facebook,
(
SELECT Count(DISTINCT user_id)
FROM github_user_infos
WHERE user_id IN(SELECT id FROM target_user_ids)) AS github,
(
SELECT Count(DISTINCT user_id)
FROM user_associated_accounts
WHERE provider_name = 'twitter'
AND user_id IN(SELECT id FROM target_user_ids)) AS twitter
To get a count of the number of non-admin users who have read the welcome PM sent by the discobot user, you could try this:
SELECT
COUNT(1) AS number_of_opens
FROM topics t
JOIN topic_users tu
ON tu.topic_id = t.id
JOIN users u
ON u.id = tu.user_id
WHERE t.user_id = -2
AND u.admin = false
AND tu.last_read_post_number IS NOT NULL
Hello! Help me please.
I need to get the topics and sort them by update date.
My query:
-- [params]
-- string :ord
SELECT distinct t.*
FROM topics t
WHERE posts_count = 1 and deleted_at is null
ORDER BY updated_at :ord
But it does not work:
PG::UndefinedObject: ERROR: type "updated_at" does not exist
LINE 12: ORDER BY updated_at 'asc'
^unfortunately it doesn’t work either:
PG::InvalidSchemaName: ERROR: schema "t" does not exist
LINE 12: ORDER BY t.updated_at 'asc'
^This method did not help, as well as others tried by me.
But it works if you remove the parameter from the request, for example:
-- [params]
-- string :ord
SELECT distinct t.*
FROM topics t
WHERE posts_count = 1 and deleted_at is null
ORDER BY t.updated_at descYes., only declare “params” if you will USE them on query.
You should read this: Declaring Parameters
Good luck
thanks for manual. But I’m interested in, is it possible to pass the asc or desc parameter to ORDER BY?
-- [params]
-- string :ord
SELECT distinct *
FROM topics t
ORDER BY t.updated_at :ordI think it is not possible.
Maybe @Simon_Cossar may help us here.
I think “asc” and “desc” are keywords, not values, so you can’t pass them by parameters. 
I don’t think it’s possible to pass a keyword as a parameter, but a boolean :desc parameter could be used in a CASE expression.
--[params]
-- boolean :desc = false
SELECT
*
FROM generate_series(1, 10) AS num
ORDER BY
CASE WHEN :desc THEN num END DESC,
CASE WHEN NOT :desc THEN num END ASC
Okay excellent! This worked! Thanks so much.
Now for more granularity. How can I get this number, but just for the last X months? I tried copying a part of query from another query created within the plugin, but no go.
Any thoughts?
Try this:
-- [params]
-- int :months_ago = 1
WITH query_period as (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)
SELECT
COUNT(1) AS number_of_opens
FROM topics t
JOIN topic_users tu
ON tu.topic_id = t.id
RIGHT JOIN query_period qp
ON t.created_at >= qp.period_start
JOIN users u
ON u.id = tu.user_id
WHERE t.user_id = -2
AND u.admin = false
AND tu.last_read_post_number IS NOT NULL
AND t.created_at <= qp.period_end
I could use some help. I don’t know my head from my butt with SQL.
I am admining a board for my labor union. I’m an elected rep, but I’m not in leadership. The reason I’m admining it is because I have experience with discourse already, I set it up to modernize how we communicate internally (team collaboration), and I’m the only one at the moment (of 20 of us) who really has any clue how to do anything more advanced in a tech related behind the scenes capacity greater than using a web builder to build a website. Point is our group is end user tech savy, but not “behind the scenes.”
As this is a work space for all of us, including leadership, I’ve setup a couple categories for them to work in which I shouldn’t be able to snoop in. (call it above my pay grade).
Since there’s no real way to hide content from an admin, I created a theme component with CSS to:
Bottom line, I’m creating a series of log and audit trails to be available should they wish to check that I’m not snooping.
Basically, If I ever tamper with the CSS I wrote to hide this stuff they can see and ask me to explain myself.
What I need help with:
if I ever view the page with the css style-sheets disabled, I can see the URLs to the topics. Then obviously I can open the topics directly. What I want is a data explorer query they (executive team who all get admin access) can run from time to time that shows topics viewed within a category by any user not a member of the “executive” group.
They have no idea this can be done, but I want every base covered. I want to future proof this beyond me. As best as I can with the tools available.
So again, I need a data explorer query to do:
This combined with the CSS white out I’ve applied creates enough of a paper trail that says if someone (me or anyone not on the executive team who may be given admin access in the future), has viewed topics within that category and they are not part of the executive team, they really have had to go out of their way and it’s intentional snooping.
Can anyone help with this?
sorted by recent activity
SELECT
t.id AS topic_id,
t.user_id
FROM
topics t
JOIN posts p ON t.id = p.topic_id
WHERE
t.archetype = 'private_message'
AND t.user_id > 0
AND t.reply_count > 1
AND NOT t.closed
GROUP BY
t.id
ORDER BY
t.updated_at DESC
I think you should open new topic about this…
But, you may try (and learn SQL) 
Point 1:
-- [params]
-- string :gname = executive
-- string :uname = SidV
SELECT u.username, g.id as "group id", g.name
FROM group_users gu, users u, groups g
WHERE g.id = gu.group_id
AND u.id = gu.user_id
AND g.name ILIKE :gname
AND u.username ILIKE :uname
I can’t understand your point… what do you need?
Check the query list and try to adapt some query for your need.
Basically write a query to display any topics read in category 1 or 2 by any user not assigned to group A