Estatísticas diárias, semanais ou totais por usuário em um intervalo de tempo especificado

Comunidade Dados e relatórios
1 
-- cobertura: 'week', 'all' ou 'date'
-- [parâmetros]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
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.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
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.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END

Você pode usar esta consulta para obter estatísticas diárias, semanais ou totais por usuário em um intervalo de tempo especificado.
Parâmetros de entrada:

  • start_date (string) ex. ‘2019-12-31’
  • end_date (string) ex. ‘2019-12-31’
  • coverage (enum) uma das opções: 1) ‘date’ para diário, 2) ‘week’ para semanal ou 3) ‘all’ para total

Campos de saída:

  • id (user_id)
  • username
  • created_at (data de criação do usuário)
  • period (string de data para cobertura ‘date’, número da semana (ex. 26 a 31 de dezembro = 52 para cobertura ‘week’) ou -1 para cobertura ‘all’)
  • replies (número de publicações que não são a primeira postagem de um tópico)
  • topics
  • likes_received
  • likes_given
  • posts_read
  • time_read
  • visits

Provavelmente você precisará usar a API para acessar todos os resultados desta consulta (especialmente para dados diários em um longo período). Veja esta postagem para mais detalhes sobre como acionar consultas via API.

Você pode usar esta consulta para gerar um conjunto de dados de engajamento diário dos usuários.

7 curtidas