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