# Queries for inactive and active users?

**URL:** <https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819>\
**Category:** Support\
**Created:** [2016年二月18日 19:14 UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819 "2016-02-18T19:14:39Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![techAPJ](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/techapj/32/342990_2.png) [@techAPJ](https://meta.discourse.org/u/techAPJ)\
**Post date:** [2016年二月22日 19:02 UTC](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819/3 "2016-02-22T19:02:02Z")

</div>

> [@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.

---

_[View the full topic](https://meta.discourse.org/t/queries-for-inactive-and-active-users/39819)._