Estadísticas diarias, semanales o totales por usuario en un rango de tiempo especificado

Comunidad Datos e informes
1

– cobertura: ‘semana’, ‘todo’ o ‘fecha’
– [parámetros]
– date :start_date = 2019-08-27
– date :end_date = 2019-09-30
– text :coverage = semana

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "fecha", EXTRACT(week from date_trunc('day', dd):: date) AS "semana"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.fecha, dr.semana, count(pa.*) AS "me_gusta"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.fecha and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.fecha, dr.semana, u.id
ORDER BY u.id, dr.fecha
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS respuestas, count(t.id) AS temas, COALESCE(sum(p.like_count),0) AS me_gusta_recibidos
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.fecha AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.fecha AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.fecha, dr.semana
ORDER BY u.id, dr.fecha), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS publicaciones_leídas, COALESCE(sum(time_read),0) AS tiempo_leído, COUNT(uv.*) AS visitas
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.fecha)
GROUP BY u.id, dr.fecha, dr.semana
ORDER BY u.id, dr.fecha
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'semana' THEN ps.semana::text
WHEN :coverage::text = 'todo' THEN '-1'
ELSE ps.fecha::text
END  AS periodo, sum(ps.respuestas) AS respuestas, sum(ps.temas) AS temas, sum(ps.me_gusta_recibidos) AS me_gusta_recibidos, sum(lg.me_gusta) AS me_gusta_dados, COALESCE(sum(publicaciones_leídas),0) AS publicaciones_leídas, COALESCE(sum(tiempo_leído),0) AS tiempo_leído, SUM(visitas) AS visitas
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.fecha = lg.fecha AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.fecha = ps.fecha)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'semana' THEN ps.semana::text
WHEN :coverage::text = 'todo' THEN '-1'
ELSE ps.fecha::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'semana' THEN ps.semana::text
WHEN :coverage::text = 'todo' THEN '-1'
ELSE ps.fecha::text
END

Puedes usar esto para obtener estadísticas diarias, semanales o totales por usuario en un rango de tiempo especificado.
Parámetros de entrada:

  • start_date (cadena) ej. ‘2019-12-31’
  • end_date (cadena) ej. ‘2019-12-31’
  • coverage (enum) una de 1) ‘fecha’ para diario 2) ‘semana’ para semanal o 3) ‘todo’ para total

Campos de salida:

  • id (user_id)
  • username
  • created_at (fecha de creación del usuario)
  • periodo (cadena de fecha para cobertura ‘fecha’, número de semana (ej. 26-31 dic = 52 para cobertura ‘semana’, o -1 para cobertura ‘todo’)
  • respuestas (número de publicaciones que no son la primera publicación en un tema)
  • temas
  • me_gusta_recibidos
  • me_gusta_dados
  • publicaciones_leídas
  • tiempo_leído
  • visitas

Probablemente necesites usar la API para acceder a los resultados completos de esta consulta (especialmente para datos diarios durante un largo período de tiempo). Consulta esta publicación para más detalles sobre cómo ejecutar consultas a través de la API.

Puedes usar esta consulta para generar un conjunto de datos de participación diaria de los usuarios.

7 Me gusta