# Queries for inactive and active users?
**URL:** https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819
**Category:** Support
**Created:** [February 18, 2016, 7:14pm UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819 "2016-02-18T19:14:39Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@will\_io](https://meta.discourse.org/u/will_io)
#### Post date: [February 18, 2016, 7:14pm UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819/1 "2016-02-18T19:14:39Z")
I intend to do some house cleaning on my discourse install.
I want to identify two similar types of users:
1. **Inactive users without posts** (pre-Discourse signups)
2. **Active users without posts and excessive read times** (lurkers)
I am going to get rid of #1s and seek creative ways to reach out to the #2s.
I’d like to use this thread as a way to generate some ideas on how to proceed and things I should be cautious of before moving forward.
Thank you.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [February 22, 2016, 6:24am UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819/2 "2016-02-22T06:24:11Z")
Sure @techapj can you suggest some [data explorer](https://github.com/discourse/discourse-data-explorer) queries that might help here?
---
### Author:  [@techAPJ](https://meta.discourse.org/u/techAPJ)
#### Post date: [February 22, 2016, 7:02pm UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819/3 "2016-02-22T19:02:02Z")
> [@will\_io](#):
>
> Inactive users without posts (pre-Discourse signups)
```plaintext
SELECT
u.id,
u.username_lower AS "username",
u.created_at,
u.last_seen_at
FROM users u
WHERE u.active = false
ORDER BY u.id
```
Note: Since inactive users can’t create posts, we do not need to check for posts created.
* * *
> [@will\_io](#):
>
> Active users without posts and excessive read times (lurkers)
```plaintext
WITH posts_by_user AS (
SELECT COUNT(*) AS posts, user_id
FROM posts
GROUP BY user_id
), posts_read_by_user AS (
SELECT SUM(posts_read) AS posts_read, user_id
FROM user_visits
GROUP BY user_id
)
SELECT
u.id,
u.username_lower AS "username",
u.created_at,
u.last_seen_at,
COALESCE(pbu.posts, 0) AS "posts_created",
COALESCE(prbu.posts_read, 0) AS "posts_read"
FROM users u
LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id
LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id
WHERE u.active = true
AND posts IS NULL
AND posts_read > 100
ORDER BY u.id
```
Adjust `posts_read (100)` as per your requirement.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [December 23, 2018, 11:20am UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819/5 "2018-12-23T11:20:31Z")
---
### Author:  [@jomaxro](https://meta.discourse.org/u/jomaxro)
#### Post date: [December 23, 2018, 3:26pm UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819/6 "2018-12-23T15:26:14Z")
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [December 24, 2018, 1:35pm UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819/7 "2018-12-24T13:35:29Z")
Possibly worth adding these queries to the “stock queries” that ship with [data explorer](https://meta.discourse.org/t/32566?silent=true)
---
### Author:  [@rishabh](https://meta.discourse.org/u/rishabh)
#### Post date: [December 28, 2018, 6:51am UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819/9 "2018-12-28T06:51:34Z")
I modified the second query to accept a parameter and added both queries in:
[https://github.com/discourse/discourse-data-explorer/commit/7de1e5a68e1ddd1ca894c054647dc5da54a700c6](https://github.com/discourse/discourse-data-explorer/commit/7de1e5a68e1ddd1ca894c054647dc5da54a700c6)