Похоже, что указанные вами входные данные не соответствуют ожидаемому результату. Если вы хотите получить данные для одного пользователя, в качестве отправной точки может подойти следующий запрос. Он использует общие табличные выражения (CTE), чтобы сделать запрос более читаемым и простым в написании. Тот же шаблон можно использовать для добавления дополнительных данных в результат.
Один момент, в котором я не уверен: хотите ли вы получить сумму данных за определенный день или количество действий, совершенных в каждый из дней. Например, хотите ли вы узнать, что на 2019-10-31 у пользователя было всего 20 постов за все время его пребывания на сайте, или вы хотите узнать, что на 2019-10-31 пользователь создал ровно 3 поста? В текущей структуре запрос возвращается именно второй вариант.
--[params]
-- string :username
-- date :start_date
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, NOW()::date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = 'regular'
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts,
SUM(array_length(regexp_split_to_array(raw, '\s'), 1)) AS word_count
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND p.deleted_at IS NULL
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(word_count, 0) AS word_count,
COALESCE(likes_received_count, 0) AS likes_received
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
ORDER BY d.day