Queries for inactive and active users?

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

6 Likes