Top Quality Users query always empty

David_Abitbol · January 13, 2022, 4:26pm

Hi!

We are trying to run the Top Quality Users query in data explorer. Whatever we try, the number of results is always empty

The query is here:

github.com

SidVal/discourse-data-explorer/blob/queries/queries/top-quality-users.sql

-- source: https://meta.discourse.org/t/43516/30
-- Post scores are calculated based on reply count, likes, incoming links, bookmarks, average time (reading?) and read count.

SELECT 
    sum(p.score) / count(p) as "average score per post", 
    count(p.id) as post_count, 
    p.user_id
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '6 month'
  AND NOT u.admin
  AND NOT u.blocked
  AND u.active
GROUP by user_id, u.views
HAVING count(p.id) > 50
ORDER BY sum(p.score) / count(p) DESC
LIMIT 20

-- Modified by SidV: "u.blocked" does not exist anymore.
SELECT

This file has been truncated. show original

I tried removing the conditions for the user (u.admin, etc.) but still nothing

The query plan is here as well:

Limit (cost=191.87…191.92 rows=20 width=24)
→ Sort (cost=191.87…191.92 rows=20 width=24)
Sort Key: ((sum(p.score) / (count(p.*))::double precision)) DESC
→ HashAggregate (cost=190.58…191.43 rows=20 width=24)
Group Key: p.user_id, u.views
Filter: (count(p.id) > 50)
→ Hash Join (cost=8.79…177.19 rows=893 width=1055)
Hash Cond: (p.user_id = u.id)
→ Seq Scan on posts p (cost=0.00…165.80 rows=960 width=1051)
Filter: (created_at >= (CURRENT_DATE - ‘P6M’::interval))
→ Hash (cost=7.29…7.29 rows=120 width=8)
→ Seq Scan on users u (cost=0.00…7.29 rows=120 width=8)
Filter: ((NOT admin) AND active)

codinghorror · January 14, 2022, 12:01am

Should this query be working @tshenry? We ship it by default?

tshenry · January 14, 2022, 12:28am

So there are a few things to unpack here.

This is a fork of the official data explorer plugin
The query appears twice in that file, the first time including u.blocked which does not exist anymore as mentioned in the comment at the end of the snippet in the OP
We do ship a version of the query in the official plugin

@David_Abitbol you will either want to reach out to the maintainer of that fork, or ideally switch to using the official version at GitHub - discourse/discourse-data-explorer: SQL Queries for admins in Discourse

David_Abitbol · January 14, 2022, 1:46am

Thanks Taylor.

We are using the Saas version so I don’t know if it’s available. In any case, I’ll go find the query. Thanks!