# Find users who read specific topics
**URL:** https://meta.discourse.org/t/find-users-who-read-specific-topics/100657
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [October 28, 2018, 4:04am UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657 "2018-10-28T04:04:16Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@Henry\_Cooper](https://meta.discourse.org/u/Henry_Cooper)
#### Post date: [October 28, 2018, 4:04am UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/1 "2018-10-28T04:04:16Z")
How can I use [data explorer](https://meta.discourse.org/t/32566?silent=true) 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 name to be in the output.
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [October 28, 2018, 10:50pm UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/2 "2018-10-28T22:50:13Z")
For the users having read a certain topic, you need to join on the “`topic_user`” table.
For the categories, you need to filter the topics of said categories 😉
---
### Author:  [@mikechristopher](https://meta.discourse.org/u/mikechristopher)
#### Post date: [October 29, 2018, 4:55pm UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/4 "2018-10-29T16:55:30Z")
For Topic Participants you could use something like this
```plaintext
-- [params]
-- null string_list :topic_id
WITH user_data AS (
SELECT u.username, ue.email, tu.topic_id FROM users u
LEFT JOIN topic_users tu ON u.id = tu.user_id AND tu.posted = true
LEFT JOIN user_emails ue on u.id = ue.user_id
)
SELECT * FROM user_data WHERE topic_id = :topic_id
```
You would then just need to enter the relevant topic id in the box that is required.
---
### Author:  [@HAWK](https://meta.discourse.org/u/HAWK)
#### Post date: [October 29, 2018, 9:07pm UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/6 "2018-10-29T21:07:14Z")
I specifically linked to one there. It looks pretty close to what you are asking for. Can you run it and tell us what more info you need and we can likely edit it.
---
### Author:  [@Henry\_Cooper](https://meta.discourse.org/u/Henry_Cooper)
#### Post date: [October 29, 2018, 9:28pm UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/7 "2018-10-29T21:28:29Z")
> [@HAWK](#):
>
> I specifically linked to one there
Sorry about that, never noticed
I dont understand why user is a number, where I should specify the topic id,
and also when I run the query nothing shows up? (probably because I dont know how to use it)
---
### Author:  [@Henry\_Cooper](https://meta.discourse.org/u/Henry_Cooper)
#### Post date: [October 29, 2018, 9:31pm UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/8 "2018-10-29T21:31:17Z")
Thank you, this one is going to be pretty useful for me in the future when my forum is bigger.
Does this also include people who have liked the posts in the topic, or only those who replied?
If it does not include likers, how can we add them?
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [October 29, 2018, 11:23pm UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/9 "2018-10-29T23:23:28Z")
You can get more data from that query by selecting for more fields from the `topic_users` table. To find whether the user liked a post, or posted in the topic, try this:
```plaintext
-- [params]
-- null string_list :topic_id
WITH user_data AS (
SELECT u.username, ue.email, tu.topic_id, tu.posted, tu.liked FROM users u
LEFT JOIN topic_users tu ON u.id = tu.user_id AND tu.posted = true
LEFT JOIN user_emails ue on u.id = ue.user_id
)
SELECT * FROM user_data WHERE topic_id = :topic_id
```
---
### Author:  [@Henry\_Cooper](https://meta.discourse.org/u/Henry_Cooper)
#### Post date: [October 30, 2018, 1:57am UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/10 "2018-10-30T01:57:05Z")
This only shows me if the users who posted in the topic likes the post. Doesnt show if someone just comes in likes and leaves without posting?
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [October 30, 2018, 2:21am UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/11 "2018-10-30T02:21:56Z")
> [@Henry\_Cooper](#):
>
> This only shows me if the users who posted in the topic likes the post. Doesnt show if someone just comes in likes and leaves without posting?
That’s right. You can alter the query so that it doesn’t only select users who have posted by removing this part:
```plaintext
AND tu.posted = true
```
Try this:
```plaintext
-- [params]
-- int :topic_id
WITH user_data AS (
SELECT u.username,
ue.email,
tu.topic_id,
tu.posted,
tu.liked
FROM users u
JOIN topic_users tu ON u.id = tu.user_id
JOIN user_emails ue on u.id = ue.user_id
ORDER BY tu.last_visited_at DESC
)
SELECT * FROM user_data WHERE topic_id = :topic_id
```
---
### Author:  [@Henry\_Cooper](https://meta.discourse.org/u/Henry_Cooper)
#### Post date: [October 30, 2018, 2:29am UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/12 "2018-10-30T02:29:37Z")
Thank you so much brother you just sent me exactly what I needed. Not only does it scrape users who liked and commented, but it also shows me who viewed it!!
Respects!
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [November 29, 2018, 2:29am UTC](https://meta.discourse.org/t/find-users-who-read-specific-topics/100657/13 "2018-11-29T02:29:42Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.