I don’t think the inputs you have listed match the output you are hoping to get. If you would like to get data for a single user, something like the query below might work as a starting point. It’s using Common Table Expressions to make the query easier to read and write. The same pattern could be followed to add more data to the results.
One thing I wasn’t sure of is if you want the sum of the data for a given day, or the number of actions that took place on each day. For example, do you want to know that on 2019-10-31 a user has created a total of 20 posts in all the days they have been on the site, or do you want to know that on 2019-10-31 the user created exactly 3 posts? The way the query is currently structured, it’s returning the latter result.
--[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
Para aumentar la actividad de los usuarios, planeamos organizar un desafío para identificar al usuario más activo de nuestro foro y otorgarle un premio.
Por ello, quisimos obtener algunos datos adicionales para hacer el desafío aún más interesante.
Los datos que tenemos, utilizando la consulta de @simon, son: Temas, Publicaciones, Conteo de palabras y Likes recibidos.
Algunos datos adicionales incluyen:
Likes otorgados: Cantidad de likes que ha dado el usuario.
Visitas: Cuántas veces el usuario ha abierto el foro.
Tiempo de lectura: Cuánto tiempo pasa el usuario en el foro.
Seguidos y Seguidores
Vistas de temas: Número de vistas que reciben todos los temas creados por el usuario.
Respuestas a temas: Número de respuestas (publicaciones) que reciben todos los temas creados por el usuario.
Por favor, ayúdenos para que podamos obtener estos datos adicionales.
Para el ponderado de cada elemento, planeamos usar Excel. ¿Tienen algún consejo sobre cómo ponderar cada elemento para elegir al ganador?
Queremos que este desafío sea serio pero divertido. Los mejores usuarios son la combinación de los más activos, los más útiles y los más populares.
Una vez más, muchas gracias por su ayuda.
Nota: ¿Podemos obtener los datos por grupos, de modo que solo tengamos que ingresar el ID del grupo? Nuestro plan es colocar a todos los participantes en un grupo. Esta vez, tenemos que ingresar el ID de usuario de cada participante uno por uno.
Soy otro manchesteriano
Mi empresa está buscando organizar un concurso similar internamente para fomentar la participación de nuestros empleados con nuestra comunidad.
¿Lograste llevar a cabo el concurso? ¿Cómo terminaste extrayendo los datos? Y, finalmente, ¿cómo ponderaste las diferentes métricas de participación?
