Estadísticas del usuario más correo electrónico

Datos e informes
1

¿Cómo agregaría una columna para el tiempo de lectura, las publicaciones leídas y las visitas de usuarios a esto?

Esencialmente, me gustaría combinar la siguiente consulta con la que ha publicado aquí, además de agregar el tiempo de lectura (si es posible):

select user_id,
    count(1) as visits,
    sum(posts_read) as posts_read
from user_visits
where posts_read > 0
and visited_at > CURRENT_TIMESTAMP - INTERVAL '7 days'
group by user_id
order by visits desc, posts_read desc

Nota: Prefiero la capacidad de definir el parámetro de intervalo como lo tiene en su consulta, pero me gustaría tener el usuario de la consulta que publiqué como el correo electrónico completo (no solo el dominio del correo electrónico).

Soy nuevo jugando con la escritura de consultas y entiendo los parámetros y pude modificar el suyo para que sea la dirección de correo electrónico completa. Simplemente no puedo combinar las columnas de visitas y publicaciones leídas de la consulta que compartí, ni agregar el tiempo de lectura.

2 Me gusta
2

Creo que tener los resultados por usuario en lugar de agrupados por dominio de correo electrónico hace que esta sea una consulta ligeramente diferente. Separemos esto en su propio tema. :+1:

1 me gusta
3

Creo que algo como esto debería funcionar:

-- [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
1 me gusta
4

¡Esto es perfecto! Muchas gracias.

1 me gusta