# Data & reporting **URL:** https://meta.discourse.org/c/community-building/data-reporting/148.md?page=22 [Latest](https://meta.discourse.org/latest.md) · [Categories](https://meta.discourse.org/categories.md) · [Tags](https://meta.discourse.org/tags.md) **Page:** 23 --- ## [Find users who read specific topics](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657)
**Author:** [@Henry\_Cooper](https://meta.discourse.org/u/Henry_Cooper)\ **Replies:** 9\ **Last updated:** [October 30, 2018, 2:29am UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657 "2018-10-30T02:29:37Z")
How can I use data explorer plugin to find out if users read/engaged with certain topics and certain categories? I tried searching the forum but no luck on finding it yet. I also need the users email and first/last nam… --- ## [Established users with trust-level locked to 0 or 1](https://meta.discourse.org/t/established-users-with-trust-level-locked-to-0-or-1/275037)
**Author:** [@ChrisBeach](https://meta.discourse.org/u/ChrisBeach)\ **Replies:** 0\ **Last updated:** [October 22, 2018, 12:19pm UTC](https://meta.discourse.org/t/established-users-with-trust-level-locked-to-0-or-1/275037 "2018-10-22T12:19:40Z")
Established users with trust-level locked to 0 or 1: -- \[params\] -- int :min\_visited\_days = 30 -- int :min\_posts = 1 SELECT id AS user\_id, manual\_locked\_trust\_level, days\_visited, post\_count FROM … --- ## [Can I access Discourse forum Database to use for PowerBI reporting?](https://meta.discourse.org/t/can-i-access-discourse-forum-database-to-use-for-powerbi-reporting/99582)
**Author:** [@sarayasmeen](https://meta.discourse.org/u/sarayasmeen)\ **Replies:** 4\ **Last updated:** [October 16, 2018, 4:37am UTC](https://meta.discourse.org/t/can-i-access-discourse-forum-database-to-use-for-powerbi-reporting/99582 "2018-10-16T04:37:58Z")
Hi we use Discourse as a forum for discussing about various company’s products, its releases, usage and issues. we have categorized it several categories and tags to make the topic relevant. I am trying to create a repor… --- ## [Top 10 referrers over the last month](https://meta.discourse.org/t/top-10-referrers-over-the-last-month/275145)
**Author:** [@AstonJ](https://meta.discourse.org/u/AstonJ)\ **Replies:** 0\ **Last updated:** [October 12, 2018, 1:04am UTC](https://meta.discourse.org/t/top-10-referrers-over-the-last-month/275145 "2018-10-12T01:04:27Z")
Another one I can’t take any credit for - ripped directly out of DC’s dashboard :smiley: Top 10 referrers over the last month SELECT COUNT(\*) AS count\_all, incoming\_domains.name AS incoming\_domains\_name FROM "incomi… --- ## [Latest uploads](https://meta.discourse.org/t/latest-uploads/275144)
**Author:** [@AstonJ](https://meta.discourse.org/u/AstonJ)\ **Replies:** 0\ **Last updated:** [October 10, 2018, 11:16pm UTC](https://meta.discourse.org/t/latest-uploads/275144 "2018-10-10T23:16:06Z")
Latest uploads SELECT id, user\_id, original\_filename, created\_at, url, extension FROM uploads order by created\_at desc LIMIT 50 Though it would be nice if they show in the results rather than… --- ## [Attempting to create Monthly Leaderboard query - head exploding](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679)
**Author:** [@jerdog](https://meta.discourse.org/u/jerdog)\ **Replies:** 18\ **Last updated:** [October 8, 2018, 10:22pm UTC](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679 "2018-10-08T22:22:31Z")
I’m trying to build a query for a leaderboard comprising the things that matter to us during the last month: Likes received Topics solved Badges gained I started with the User Participation query, stripped out what I … --- ## [List of users by who’s solved the most topics/threads](https://meta.discourse.org/t/list-of-users-by-who-s-solved-the-most-topics-threads/275143)
**Author:** [@AstonJ](https://meta.discourse.org/u/AstonJ)\ **Replies:** 0\ **Last updated:** [October 8, 2018, 12:02am UTC](https://meta.discourse.org/t/list-of-users-by-who-s-solved-the-most-topics-threads/275143 "2018-10-08T00:02:06Z")
List of users by who’s solved the most topics/threads Change within\_number\_of\_months to how many months you want to query for. So 3 will show you results from the last three months, etc. -- \[params\] -- int :within\_num… --- ## [Looking to find the number of content shares in the last month](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050)
**Author:** [@jerdog](https://meta.discourse.org/u/jerdog)\ **Replies:** 6\ **Last updated:** [September 27, 2018, 3:46am UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050 "2018-09-27T03:46:27Z")
I am working on a few queries for management, and one they want to know relates to the number of shared content, i.e. someone shares a link and it’s clicked. Here’s what I created but it’s giving me a number that doesn’t… --- ## [Lurkers who become members](https://meta.discourse.org/t/lurkers-who-become-members/275035)
**Author:** [@JanJoost](https://meta.discourse.org/u/JanJoost)\ **Replies:** 0\ **Last updated:** [September 9, 2018, 2:52pm UTC](https://meta.discourse.org/t/lurkers-who-become-members/275035 "2018-09-09T14:52:09Z")
Lurkers who become members Hi all, I’m rather new to this, but boy - what a cool tool this is! Here’s a (very simple!) qeuery I created which allows you to easily identify lurkers who became member in the last so many … --- ## [Tracking who sent private messages](https://meta.discourse.org/t/tracking-who-sent-private-messages/95607)
**Author:** [@nexo](https://meta.discourse.org/u/nexo)\ **Replies:** 1\ **Last updated:** [August 25, 2018, 12:31am UTC](https://meta.discourse.org/t/tracking-who-sent-private-messages/95607 "2018-08-25T00:31:52Z")
Hi, recently my site has started growing and users have been messaging daily and I was wondering if it was possible to get a list of the users that PM’ed today (or a certain day/date) using the Data Explorer plugin? I ju… --- ## [Is there any way of telling how popular a Theme is?](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796)
**Author:** [@merefield](https://meta.discourse.org/u/merefield)\ **Replies:** 4\ **Last updated:** [August 2, 2018, 9:02pm UTC](https://meta.discourse.org/t/is-there-any-way-of-telling-how-popular-a-theme-is/93796 "2018-08-02T21:02:31Z")
I started messing around in Data Explorer, but I’m not certain the right fields exist on the database?.. The query so far: select u.username, t.id, t.name from users u left join user\_options o on u.id = o.user\_id left … --- ## [Statistics to breakdown mobile page views vs desktop/tablet](https://meta.discourse.org/t/statistics-to-breakdown-mobile-page-views-vs-desktop-tablet/93210)
**Author:** [@merefield](https://meta.discourse.org/u/merefield)\ **Replies:** 2\ **Last updated:** [July 26, 2018, 2:40pm UTC](https://meta.discourse.org/t/statistics-to-breakdown-mobile-page-views-vs-desktop-tablet/93210 "2018-07-26T14:40:29Z")
Is there any way to see the breakdown of page views by mobile versus desktop views? --- ## [How to group by date only in data explorer](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868)
**Author:** [@John\_Waltrip1](https://meta.discourse.org/u/John_Waltrip1)\ **Replies:** 4\ **Last updated:** [July 22, 2018, 8:23am UTC](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868 "2018-07-22T08:23:49Z")
I’m trying to get the number of user signups by day for this month and run into trouble when trying to group on the created\_at field because it includes the timestamp. Anyone know how to just group on the date? select … --- ## [Stats for group members](https://meta.discourse.org/t/stats-for-group-members/275036)
**Author:** [@kinocharlino](https://meta.discourse.org/u/kinocharlino)\ **Replies:** 4\ **Last updated:** [July 18, 2018, 6:48pm UTC](https://meta.discourse.org/t/stats-for-group-members/275036 "2018-07-18T18:48:14Z")
I’m looking for a query that would give me usage/participation stats for all users in a specific group. We have volunteers who help the community and have them all in primary group “ninjas.” I’m looking to get a table wi… --- ## [Users who don’t have a particular badge](https://meta.discourse.org/t/users-who-don-t-have-a-particular-badge/275031)
**Author:** [@John\_Waltrip1](https://meta.discourse.org/u/John_Waltrip1)\ **Replies:** 3\ **Last updated:** [July 17, 2018, 10:36pm UTC](https://meta.discourse.org/t/users-who-don-t-have-a-particular-badge/275031 "2018-07-17T22:36:43Z")
I’m trying to put together a query that will show users of a group that don’t have a particular badge, like Certified. Something along the lines of "give me all the users from group VIP where badge Certified does not ex… --- ## [Help with creating a query to randomly check PMs for certain terms](https://meta.discourse.org/t/help-with-creating-a-query-to-randomly-check-pms-for-certain-terms/275030)
**Author:** [@DNSTARS](https://meta.discourse.org/u/DNSTARS)\ **Replies:** 4\ **Last updated:** [July 12, 2018, 3:48pm UTC](https://meta.discourse.org/t/help-with-creating-a-query-to-randomly-check-pms-for-certain-terms/275030 "2018-07-12T15:48:16Z")
How best do you think to fetch 10% of user to user PM messages in the last 30 days I’m trying to find a “randomise” function that could be called upon? I run a website where people talk about largely illegal stuff - dr… --- ## [Badge for replying to a topic in a specific category](https://meta.discourse.org/t/badge-for-replying-to-a-topic-in-a-specific-category/281538)
**Author:** [@yanokwa](https://meta.discourse.org/u/yanokwa)\ **Replies:** 0\ **Last updated:** [June 20, 2018, 3:31am UTC](https://meta.discourse.org/t/badge-for-replying-to-a-topic-in-a-specific-category/281538 "2018-06-20T03:31:23Z")
Here’s a badge query for someone who replies to a post in a category (id=19). The post\_number \> 1 makes sure that it’s a reply, not the first post. SELECT DISTINCT ON (p.user\_id) p.user\_id, p.id post\_id, p.created\_at gr… --- ## [A badge granted when someone posts a reply in a single specified topic](https://meta.discourse.org/t/a-badge-granted-when-someone-posts-a-reply-in-a-single-specified-topic/276673)
**Author:** [@downey](https://meta.discourse.org/u/downey)\ **Replies:** 7\ **Last updated:** [June 19, 2018, 10:10pm UTC](https://meta.discourse.org/t/a-badge-granted-when-someone-posts-a-reply-in-a-single-specified-topic/276673 "2018-06-19T22:10:21Z")
Anyone want a (hopefully easy) challenge? A badge granted when someone posts a reply in a single specified topic. (i.e., A “Please introduce yourself here” topic.) --- ## [How to filter out a group?](https://meta.discourse.org/t/how-to-filter-out-a-group/275141)
**Author:** [@Kyle\_Selby](https://meta.discourse.org/u/Kyle_Selby)\ **Replies:** 2\ **Last updated:** [May 14, 2018, 7:07pm UTC](https://meta.discourse.org/t/how-to-filter-out-a-group/275141 "2018-05-14T19:07:20Z")
Hello all, I am attempting to filter out a group I created named “Employees” from this data explorer tab, what am I doing wrong? How do I exclude group that I have created?: SELECT sum(p.score) / count(p) as "… --- ## [How do I see who is marking solved and on what?](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981)
**Author:** [@Sean\_R](https://meta.discourse.org/u/Sean_R)\ **Replies:** 10\ **Last updated:** [May 3, 2018, 3:15pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981 "2018-05-03T15:15:17Z")
We are allowing our Trust Level 3 customers to mark Solved, but we have no good way to see how many they are doing. For example, see if a small number of this group are actually doing this work. I did see … and I do … --- ## [User to user vs User to user with reply](https://meta.discourse.org/t/user-to-user-vs-user-to-user-with-reply/86528)
**Author:** [@testingsoftware](https://meta.discourse.org/u/testingsoftware)\ **Replies:** 3\ **Last updated:** [May 2, 2018, 7:50am UTC](https://meta.discourse.org/t/user-to-user-vs-user-to-user-with-reply/86528 "2018-05-02T07:50:11Z")
Hi, I was wondering what is the difference between the reports: Message User to User and User to User with replies. for the same period, the first shows 107 and the second one shows 955. Is it that user-to-user show… --- ## [Trying to query to find user custom fields filled out](https://meta.discourse.org/t/trying-to-query-to-find-user-custom-fields-filled-out/86472)
**Author:** [@jerdog](https://meta.discourse.org/u/jerdog)\ **Replies:** 3\ **Last updated:** [May 1, 2018, 12:22am UTC](https://meta.discourse.org/t/trying-to-query-to-find-user-custom-fields-filled-out/86472 "2018-05-01T00:22:53Z")
I have a data explorer query where I am trying to find all who have filled out user custom field 1-4, and would love it to only show those with all 4 as columns for each user, but my postgre skills are limited and I have… --- ## [Topics solved by regular users and staff](https://meta.discourse.org/t/topics-solved-by-regular-users-and-staff/275127)
**Author:** [@Jp\_Robotshop](https://meta.discourse.org/u/Jp_Robotshop)\ **Replies:** 0\ **Last updated:** [April 16, 2018, 4:41pm UTC](https://meta.discourse.org/t/topics-solved-by-regular-users-and-staff/275127 "2018-04-16T16:41:44Z")
Indeed :slight\_smile: Kinda struggled with it but found a way to make it work. So, here you go! -- \[params\] -- int :months\_ago = 0 WITH query\_period AS ( SELECT date\_trunc('month', CURRENT\_DATE) - INTERVAL ':months\_ag… --- ## [User Digest options](https://meta.discourse.org/t/user-digest-options/275028)
**Author:** [@robmc](https://meta.discourse.org/u/robmc)\ **Replies:** 0\ **Last updated:** [April 3, 2018, 1:41pm UTC](https://meta.discourse.org/t/user-digest-options/275028 "2018-04-03T13:41:49Z")
User Digest options It may not be the best way to do it (I still need to find a way to tell it to convert minutes to “Daily”, “Weekly” etc) but I find this information useful in order to know what digests my members are … --- ## [Trying to figure out Page Views queries](https://meta.discourse.org/t/trying-to-figure-out-page-views-queries/83986)
**Author:** [@jerdog](https://meta.discourse.org/u/jerdog)\ **Replies:** 2\ **Last updated:** [March 27, 2018, 9:09pm UTC](https://meta.discourse.org/t/trying-to-figure-out-page-views-queries/83986 "2018-03-27T21:09:49Z")
So I’m reviewing the page views, and trying to identify queries for “Content Views” vs “User Visits” so I can gather some metrics around those 2, and the only thing I can figure out is using the application\_requests tabl… --- ## [User(s) in a Group who Haven't Voted](https://meta.discourse.org/t/user-s-in-a-group-who-havent-voted/82918)
**Author:** [@simon](https://meta.discourse.org/u/simon)\ **Replies:** 0\ **Last updated:** [March 13, 2018, 5:50pm UTC](https://meta.discourse.org/t/user-s-in-a-group-who-havent-voted/82918 "2018-03-13T17:50:24Z")
User(s) in a Group who Haven’t Voted This will give you the users who belong to a named group who have not voted in any open topics. You’ll need to supply the value for the group\_name parameter. -- \[params\] -- string :g… --- ## [See what categories new users (& other trust levels) post in most](https://meta.discourse.org/t/see-what-categories-new-users-other-trust-levels-post-in-most/80494)
**Author:** [@anon61128213](https://meta.discourse.org/u/anon61128213)\ **Replies:** 3\ **Last updated:** [February 13, 2018, 10:39pm UTC](https://meta.discourse.org/t/see-what-categories-new-users-other-trust-levels-post-in-most/80494 "2018-02-13T22:39:57Z")
Is there a way to see which categories are most used by users that belong to a certain trust level? I’d especially be interested in what categories new users are most likely to start a topic in, but also where they post… --- ## [Topics solved by regular users](https://meta.discourse.org/t/topics-solved-by-regular-users/275120)
**Author:** [@simon](https://meta.discourse.org/u/simon)\ **Replies:** 0\ **Last updated:** [February 12, 2018, 3:15pm UTC](https://meta.discourse.org/t/topics-solved-by-regular-users/275120 "2018-02-12T15:15:00Z")
Topics solved by regular users (There is probably a way to combine this query with the staff query.) The number of topics solved by staff for a given time period, broken down by categories. The categories array can be a… --- ## [Topics solved by staff](https://meta.discourse.org/t/topics-solved-by-staff/275119)
**Author:** [@simon](https://meta.discourse.org/u/simon)\ **Replies:** 0\ **Last updated:** [February 12, 2018, 2:07pm UTC](https://meta.discourse.org/t/topics-solved-by-staff/275119 "2018-02-12T14:07:00Z")
Topics solved by staff The number of topics solved by staff for a given time period, broken down by categories. The categories array can be altered by editing this line: WHERE t.category\_id = ANY ('{46,25,43,40,44,35,22,… --- ## [Top 50 likers](https://meta.discourse.org/t/top-50-likers/275116)
**Author:** [@simon](https://meta.discourse.org/u/simon)\ **Replies:** 0\ **Last updated:** [February 12, 2018, 1:26pm UTC](https://meta.discourse.org/t/top-50-likers/275116 "2018-02-12T13:26:00Z")
Top 50 likers Returns the top 50 likers for a given monthly period. Results are ordered by like\_count. It accepts a ‘months\_ago’ parameter and defaults to 0 to give results for the current month. -- \[params\] -- int :mon… [Previous page](https://meta.discourse.org/c/community-building/data-reporting/148.md?page=21) [Next page](https://meta.discourse.org/c/community-building/data-reporting/148.md?page=23)