Ich glaube, so etwas sollte funktionieren:
-- [params]
-- string :interval = 7 days
WITH post_data AS (
SELECT
p.user_id,
COUNT(p.id) AS total_posts,
COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS topics,
COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS posts,
COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS pms
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval
AND p.user_id > 0
AND p.deleted_at ISNULL
AND t.deleted_at ISNULL
AND p.post_type <> 3
GROUP BY p.user_id
),
user_data AS (
SELECT
uv.user_id,
COUNT(*) AS visits,
SUM(posts_read) AS posts_read,
SUM(time_read)/60 AS time_read
FROM user_visits uv
WHERE uv.visited_at >= CURRENT_DATE - INTERVAL :interval
GROUP BY uv.user_id
)
SELECT
ud.user_id,
ue.email,
ud.visits,
ud.posts_read,
ud.time_read,
pd.total_posts,
pd.topics,
pd.posts,
pd.pms
FROM user_data ud
LEFT JOIN post_data pd ON pd.user_id = ud.user_id
JOIN user_emails ue ON ue.user_id = ud.user_id AND ue.primary = TRUE
ORDER BY ud.posts_read DESC