No creo que las entradas que has listado coincidan con la salida que esperas obtener. Si deseas obtener datos de un solo usuario, una consulta como la de abajo podría funcionar como punto de partida. Está utilizando Expresiones de Tabla Común (CTE) para hacer la consulta más fácil de leer y escribir. El mismo patrón podría seguirse para agregar más datos a los resultados.
Una cosa de la que no estaba seguro es si quieres la suma de los datos para un día determinado o el número de acciones que tuvieron lugar cada día. Por ejemplo, ¿quieres saber que el 2019-10-31 un usuario ha creado un total de 20 publicaciones en todos los días que ha estado en el sitio, o quieres saber que el 2019-10-31 el usuario creó exactamente 3 publicaciones? La forma en que está estructurada la consulta actualmente, está devolviendo el segundo resultado.
--[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